# coding: utf-8
import re
from collections import defaultdict
import logging
import random
import string
import traceback
from operator import and_, itemgetter
from datetime import timedelta, datetime
import uuid
import json
import xlrd
import xlwt
from django.conf import settings
from django.contrib.auth.hashers import check_password, make_password
from django.contrib.auth.models import User
from django.core.cache import cache
from django.core.paginator import Paginator, EmptyPage, PageNotAnInteger
from django.db import models, IntegrityError, transaction
from django.db.models import Q, F, Sum, Max, Count
from django.db.models.functions import Coalesce
from django.http import JsonResponse

from lib.qiniucloud.proxyCert import genCert
from project.settings import USER_TYPE, USERNAME_TYPE_PREFIX, LEVEL_CARDS, USER_LEVEL
from lib.common import GENDER_CHOICES, BasicManager, CacheKeys, UNCLAIMED
from lib.sms import sender
from services.datamodel.history.models import mm_HistoryCache
from services.datamodel.utils import Utils

from services.datamodel.share import BusinessCode
from functools import reduce
from services.datamodel.delivery.models import Delivery, DynamicDelivery, mm_Delivery, mm_DynamicDelivery
from services.datamodel.setmeal.models import mm_SetMeal

model_logger = logging.getLogger("datamodel")
import collections
from dateutil.relativedelta import relativedelta

class ModelManagerStudent(BasicManager):
    DEFAULT_PASSWORD = '123456'

    def _createInviteCode(self, studentId):
        # generate invitecode, which include 6 bits school_class.id
        # and 14 bits random digits.
        random_digit_string = ''.join(
            random.SystemRandom().choice(string.digits) for _ in range(7))
        return str(studentId) + random_digit_string
        # return  "{0:010}".format(classId) + random_digit_string

    def addStudent(self, username, realname, phonenumber=None, password=None, inviterid=None, openid=None, level=0,
                   provinceid=None, cityid=None, districtid=None, provincename=None, cityname=None, districtname=None,
                   share_code=None, avatar=None, status=0, gender=None, channel=0, evach=None, applet_openid=None,
                   unionid=None):

        if not password:
            password = self.DEFAULT_PASSWORD

        try:
            with transaction.atomic():
                new_user = User.objects.create(
                    username=username,
                    last_name=realname,
                    password=make_password(password)
                )
                evach_channel_id, evach_channel_name = 0, ''
                if evach:
                    from services.datamodel.evaluation.models import mm_EvaluationChannel
                    ec = mm_EvaluationChannel.filter(evach=evach).first()
                    if ec:
                        evach_channel_id, evach_channel_name = ec.id, ec.name
                        ec.used += 1
                        ec.save()
                student = self.model(
                    user=new_user,
                    nick_name=realname,
                    mobile=phonenumber,
                    # invite_user_id=inviterid,
                    # invite_user_expired_at=datetime.now() + timedelta(days=settings.INVITE_USER_PERIOD),
                    level=level,
                    share_code=uuid.uuid4(),
                    provinceid=provinceid,
                    cityid=cityid,
                    districtid=districtid,
                    provincename=provincename,
                    cityname=cityname,
                    districtname=districtname,
                    openid=openid,
                    avatar=avatar,
                    gender=gender if gender else 'unknown',
                    role_expired_at=datetime.now() + timedelta(days=settings.NEW_USER_VIPDAYS),
                    channel=channel,
                    eva_channel=evach_channel_id,
                    eva_channel_name=evach_channel_name,
                    applet_openid=applet_openid,
                    unionid=unionid
                )
                student.save()
                if share_code:
                    parent = mm_Student.get_or_default(share_code=share_code)
                    if parent:
                        # wni: 后来商量了暂时取消这个两个字段
                        # student.invite_user_id = parent.user_id
                        # student.invite_user_expired_at = datetime.now() + timedelta(days=settings.INVITE_USER_PERIOD)
                        # student.save()
                        # origin_exp = parent.role_expired_at if parent.role_expired_at else datetime.now()
                        if parent.role_expired_at:
                            if parent.role_expired_at < datetime.now():
                                origin_exp = datetime.now()
                            else:
                                origin_exp = parent.role_expired_at
                        else:
                            origin_exp = datetime.now()
                        parent.role_expired_at = origin_exp + timedelta(days=settings.NEW_USER_VIPDAYS)
                        parent.save()

                        if parent.level == 0 or parent.level > 6:
                            for item in mm_StudentTeam.filter(student_id=parent.id).values("parent_id", "level"):
                                mm_StudentTeam.update_or_create(student_id=student.id, parent_id=item["parent_id"],
                                                                defaults={"level": item["level"] + 1, "status": 0})
                        mm_StudentTeam.update_or_create(student_id=student.id, parent_id=parent.id,
                                                        defaults={"level": 1, "status": status})
                    else:
                        model_logger.warning("cannot find parent with share_code %s for student %s " % (
                            share_code, phonenumber))

        except IntegrityError:
            err = traceback.format_exc()
            model_logger.error(err)
            return {
                'retcode': 1,
                'msg': '该手机号码%s, 已经被注册, 请更换其他号码' % phonenumber,
            }
        except:
            err = traceback.format_exc()
            model_logger.error(err)
            return {'retcode': 1, 'msg': "未知错误"}

        return {
            'retcode': 0,
            'id': student.id,
            'user_id': student.user.id
        }

    # def kaihu_existing(self, seller_id, old_guy):
    #     """
    #     开户把一个老用户开成代理商
    #
    #     :param seller_id: 发起开户的student id
    #     :param old_guy: 被开户的老用户对象
    #     :return:
    #     """
    #
    #     if parent.level == 0 or parent.level > 6:
    #         for item in mm_StudentTeam.filter(student_id=parent.id).values("parent_id", "level"):
    #             mm_StudentTeam.update_or_create(student_id=student.id, parent_id=item["parent_id"],
    #                                             defaults={"level": item["level"] + 1, "status": 0})
    #     pass

    def add_student_if_not_exist(self, phone, openid, share_code=None, gender=None,
                                 provincename=None, cityname=None, nickname=None, avatar=None, channel=0, launch_id=0,
                                 unionid=None):
        """验证码方式登入, 若用户不存在, 则创建一个新用户"""

        username = USERNAME_TYPE_PREFIX.STUDENT + phone
        resp = dict(retcode=0, user=None)
        student = mm_Student.select_related("user").filter(mobile=phone).last()

        if student:
            resp["user"] = student.user
            resp['newuser'] = 0
        else:
            if launch_id:
                check_state = mm_StudentChannel.check_parm(launch_id)
                if check_state['retcode'] == 1:
                    resp.pop('user', None)
                    resp.update(**check_state)
                    return resp
            state = self.addStudent(username, nickname if nickname else "优势" + phone[-4:], phonenumber=phone, openid=openid,
                                    share_code=share_code, provincename=provincename, cityname=cityname, avatar=avatar,
                                    gender=gender, channel=channel, unionid=unionid)
            if state["retcode"] == 0:
                mm_StudentChannel.create_channel(student_id=state['id'], launch_id=launch_id)
                resp["user"] = User.objects.get(id=state["user_id"])
                # 记录uv
                if launch_id:
                    from services.datamodel.delivery.models import mm_DeliveryPvUv
                    from django.utils import timezone
                    uv_key = 'uv_times_' + str(state['id']) + '_' + str(launch_id)
                    day = timezone.now().strftime("%Y-%m-%d")
                    cache.set(uv_key, day, 3600 * 24)
                    mm_DeliveryPvUv.set_two_uv(launch_id, state['id'])
                    mm_DeliveryPvUv.set_one_uv(launch_id, openid)
                    res = mm_Delivery.filter(id=launch_id).first()
                    if res:
                        if res.charge <= 0:
                            mm_DeliveryPvUv.set_three_uv(launch_id, state['id'])
            else:
                resp.pop("user", None)
                resp.update(**state)
            resp['newuser'] = 1

        return resp

    def add_student_for_openid(self, openid, channel, share_code=None, gender=None, provincename=None, cityname=None,
                               nickname=None, avatar=None, evach=None, launch_id=0, launch_dynamic_id=0, unionid=None):
        resp = dict(retcode=0, user=None)
        # check_state = mm_StudentChannel.check_parm(launch_id, launch_dynamic_id)
        uid = None
        if unionid:
            uid = str(unionid)+str(random.randrange(1, 999, 3))
            openid = None
        if not unionid:
            uid = openid
        state = self.addStudent(uid, nickname, openid=openid, share_code=share_code, provincename=provincename,
                                cityname=cityname, avatar=avatar, gender=gender, channel=channel, evach=evach,
                                unionid=unionid)
        try:
            if state["retcode"] == 0:
                resp["user"] = User.objects.get(id=state["user_id"])
                resp["student"] = self.get_or_default(id=state["id"])
                if launch_id:
                    mm_StudentChannel.create_channel(student_id=state['id'], launch_id=launch_id)
                    from services.datamodel.delivery.models import mm_DeliveryPvUv
                    mm_DeliveryPvUv.set_uv(1, launch_id)
                elif launch_dynamic_id:
                    mm_StudentChannel.create_channel(student_id=state['id'], launch_dynamic_id=launch_dynamic_id)
            else:
                resp.pop("user", None)
                resp.update(**state)
        except:
            err = traceback.format_exc()
            model_logger.error(err)
        resp['newuser'] = 1
        return resp

    def add_student_for_unionid(self, unionid, channel, share_code, applet_openid=None,
                                nickname=None, avatar=None, evach=None):
        resp = dict(retcode=0, user=None)
        state = self.addStudent(unionid, nickname, unionid=unionid, applet_openid=applet_openid, share_code=share_code,
                                avatar=avatar, channel=channel, evach=evach)
        try:
            if state["retcode"] == 0:
                student = self.get_or_default(id=state["id"])
                student.source = 11
                student.save()
                resp["user"] = User.objects.get(id=state["user_id"])
                resp["student"] = student
                from services.datamodel.speech_applet.models import mm_SpeechActiveUser
                active_user = mm_SpeechActiveUser.create_active_user(resp["student"].id)
                resp['active_user_id'] = active_user.get('active_user_id')
                resp['Workplace_DNA'] = mm_StudentChannel.get_Workplace_DNA(student.id)
            else:
                resp.pop("user", None)
                resp.update(**state)
        except:
            err = traceback.format_exc()
            model_logger.error(err)
        resp['newuser'] = 1
        return resp

    def delete_student_for_openid(self, id, mobile, openid, unionid=None, is_app=None):
        # 显式的开启一个事务
        with transaction.atomic():
            save_id = transaction.savepoint()
            # 暴力回滚
            resp = dict(retcode=0, user=None)
            try:
                res = self.get_or_default(id=id)
                if unionid:
                    res.unionid = unionid
                    res.save()
                if res.openid and res.mobile and res.openid != '0':
                    if res.mobile == mobile:
                        resp["user"] = res.user
                        resp["student"] = res
                        resp["newuser"] = 0
                        resp['msg'] = 'OK'
                        return resp
                    return {'retcode': 1, 'msg': '该微信账户已经绑定!'}
                if res:
                    mobile_user = self.select_related("user").filter(mobile=mobile).last()
                    if not mobile_user:
                        # 如果手机号没有查到说明是个新用户
                        if not res.mobile:
                            res.mobile = mobile
                            res.save()
                        resp["newuser"] = 1
                        resp["user"] = res.user
                        resp["student"] = res
                    else:
                        from services.datamodel.evaluation.models import mm_Answer
                        from services.datamodel.order.models import mm_Order, mm_Bill
                        from services.datamodel.live.models import (mm_Live, mm_LiveImMsg, mm_LiveImUser, mm_LiveTally, mm_LiveGroup,
                                                                    mm_LiveInvitationUser, mm_LivePeopleFlow)
                        if is_app:
                            # 微信用户和手机用户数据都以手机用户为主
                            if res.mobile and res.mobile != str(mobile):
                                return {'retcode': 1, 'msg': "当前手机号不能绑定该微信号!"}
                            self.filter(mobile=mobile).update(age=res.age, gender=res.gender, occupation=res.occupation,
                                                              avatar=res.avatar, nick_name=res.nick_name,
                                                              eva_channel=res.eva_channel,
                                                              eva_channel_name=res.eva_channel_name, unionid=unionid)
                        else:
                            if mobile_user.openid == str(openid) or mobile_user.openid and mobile_user.openid != '0':
                                return {'retcode': 1, 'msg': '该账户已经绑定!'}
                            # 微信用户和手机用户数据都以手机用户为主
                            user_unionid = cache.get('user_unionid:'+str(openid))
                            if not user_unionid:
                                user_unionid = None
                            self.filter(mobile=mobile).update(age=res.age, gender=res.gender, occupation=res.occupation,
                                                              openid=openid, avatar=res.avatar, nick_name=res.nick_name,
                                                              eva_channel=res.eva_channel,
                                                              eva_channel_name=res.eva_channel_name, unionid=user_unionid)
                        # 直播数据
                        mm_Live.filter(student_id=res.id).update(student_id=mobile_user.id)
                        mm_LiveImMsg.filter(from_account_id=res.id).update(from_account_id=mobile_user.id)
                        mm_LiveImUser.filter(student_id=res.id).update(student_id=mobile_user.id)
                        mm_LiveTally.filter(student_id=res.id).update(student_id=mobile_user.id)
                        mm_LiveGroup.filter(student_id=res.id).update(student_id=mobile_user.id)
                        mm_LiveInvitationUser.filter(be_invited_id=res.id).update(be_invited_id=mobile_user.id)
                        mm_LiveInvitationUser.filter(invitation_id=res.id).update(invitation_id=mobile_user.id)
                        mm_LivePeopleFlow.filter(student_id=res.id).update(student_id=mobile_user.id)

                        mm_StudentChannel.update_launch(mobile_user, res)
                        mm_Bill.filter(student_id=res.id).update(student_id=mobile_user.id)
                        mm_StudentTrackRecord.filter(student_id=res.id).update(student_id=mobile_user.id)
                        mm_Answer.update_student_answer_for_id(res.id, mobile)
                        # --------
                        mm_Order.filter(user_id=res.id).update(user_id=mobile_user.id)
                        mm_StudentCapital.filter(buyer_student_id=res.id).update(buyer_student_id=mobile_user.id)
                        model_logger.error(f"微信绑定-----删除账号：mobile：{res.mobile}，id：{res.id}-----，openid:{res.openid}---"
                                           f"现账号：{mobile_user.id}，mobile:{mobile_user.mobile}，openid:{mobile_user.openid}")
                        res.delete()
                        User.objects.filter(id=res.user_id).delete()
                        resp["user"] = mobile_user.user
                        resp["student"] = self.get_or_default(id=mobile_user.id)
                        resp["newuser"] = 0
                    resp['msg'] = 'OK'
                    transaction.savepoint_commit(save_id)
            except:
                err = traceback.format_exc()
                model_logger.error(err)
                transaction.savepoint_rollback(save_id)  # 回滚
                return {'retcode': 1, 'msg': '用户数据异常'}
            transaction.clean_savepoints()
        return resp

    def delete_student_for_applet_openid(self, mobile, applet_openid):
        """小程序用户同步手机号用户"""
        with transaction.atomic():
            student = self.get(applet_openid=applet_openid)
            if student.mobile:
                if student.mobile == mobile:
                    return {"retcode": 0, "message": "绑定手机号成功", 'user': student}
                return {"retcode": 1, "message": "该用户已绑定手机号"}

            mobile_student = self.filter(mobile=mobile).first()
            if not mobile_student:
                student.mobile = mobile
                student.save()
                return {"retcode": 0, "message": "绑定手机号成功", 'user': student}

            else:
                if mobile_student.applet_openid:
                    return {"retcode": 1, "message": "该手机号已绑定用户，请更换手机号"}
                # 将applet_openid用户数据转移至手机号用户
                from services.datamodel.speech_applet.models import mm_SpeechActiveUser
                mobile_student.applet_openid = student.applet_openid
                mobile_student.unionid = student.unionid
                if not mobile_student.avatar:
                    mobile_student.avatar = student.avatar
                if not mobile_student.nick_name:
                    mobile_student.nick_name = student.nick_name
                mm_SpeechActiveUser.filter(student=student).update(student=mobile_student)
                # wni：转为老用户时会导致未绑定手机号时锁的粉失效
                # for i in mm_StudentTeam.filter(parent=student).values("student_id", "level"):
                #     for j in mm_StudentTeam.filter(student=mobile_student).values("parent_id", "level"):
                #         mm_StudentTeam.update_or_create(student_id=i['student_id'], parent_id=j["parent_id"],
                #                                     defaults={"level": j["level"] + i['level'], "status": 0})
                # mm_StudentTeam.filter(parent=student).update(parent=mobile_student)
                model_logger.error(f"小程序-----删除账号：mobile：{mobile}，id：{student.id}-----，现账号：{mobile_student.id}")
                student.delete()
                student.user.delete()
                mobile_student.save()
                return {"retcode": 0, "message": "绑定手机号成功", 'user': mobile_student}

    def get_user_for_session(self, sid):
        ret = list(self.filter(user_id=sid).values())
        return {'retcode': 0, 'data': ret[0] if len(ret) > 0 else None}

    def create_yuwei_superuser(self, level, realname, email, password):
        user = User.objects.filter(username=email).last()
        if user:
            student = user.student
            student.level = level
            student.save()
            return {'retcode': 0}
        else:
            return self.addStudent(email, realname, phonenumber=email, password=password, level=level)

    def getDirectProxy(self, studentid, needStatus=False):
        """

        :param needStatus: 表示需要返回status作为后续业务逻辑判断
        :param studentid:
        :return:
        """
        st = mm_StudentTeam.select_related("parent").filter(~Q(status=10), parent__level__in=[1, 2, 3, 4, 5, 6],
                                                            student_id=studentid).last()
        if needStatus:
            return (st.parent, st.status) if st else (None, None)
        else:
            return st.parent if st else None

    def check_account(self, username, password=None):
        """
        只有帐号，则检查该帐号是否被占用
        传入密码，则检查该帐号密码是否匹配
        """
        student = self.filter(user__username=username).last()

        resp = {'retcode': 0, 'username': username}

        if not password:
            if student:
                return {'retcode': self.AccountExist, 'msg': '帐号已存在'}
        else:
            if not student:
                return {'retcode': self.AccountNotExist, 'msg': '帐号不存在'}
            elif not check_password(password, student.user.password):
                return {'retcode': self.AccountErrorPassword, 'msg': '密码不正确'}
            else:
                resp.update(user_id=student.user.id,
                            realname=student.realname,
                            class_id=student.classid)

        return resp

    def is_unique_phonenumber_in_students(self, phonenumber, is_raise=False, msg_raise='该号码已被注册，请更换手机号'):
        """
        学生账号在学生用户中唯一
        """
        status = self.filter(mobile=phonenumber).exists()

        assert not (status and is_raise), (self.PHONE_USED, msg_raise)

        return status

    def app_kaihu(self, mobile, seller_id, level, seller_level, share_code, nick_name,
                  alipay_number, alipay_name, role_id=1, set_meal=None, platform=0):
        """

        :param share_code: 开户人的share_code
        :param seller_level:
        :param mobile:
        :param seller_id:
        :param level: 被发展人的level
        :param nick_name:
        :param alipay_number:
        :param alipay_name:
        :param role_id:
        :return:
        """
        from services.datamodel.vip.models import mm_UserAuthentication
        old_guy = self.get_or_default(mobile=mobile)
        # seller_key = CacheKeys.USER_BALANCE % (str(seller_id), str(role_id))
        # user_cards_num_seller = cache.get(seller_key)
        # if user_cards_num_seller:
        meal_dict = mm_SetMeal.get_meal(special_status=1)[level]
        set_meal_data = collections.defaultdict(list)
        for v in meal_dict:
            set_meal_data[v['serial_number']].append({'name': v['name'], 'nums': v['nums'], 'goods_id': v['goods_id']})
        for v in set_meal_data[set_meal]:
            user_cards_num_seller = self.getMyCardStats(seller_id, v['goods_id'])['balance']
            if user_cards_num_seller is None or user_cards_num_seller < v['nums']:
                return {'retcode': 1, 'msg': '对不起， 您的余卡不足!'}
        # 这里是因为他们要强行加入一个定位小班套餐，导致5和6等级上的变动，目前客户端传过来5，但是套餐取得的6，所以这里需要转换下
        level = 5
        try:
            with transaction.atomic():
                save_id = transaction.savepoint()
                username = USERNAME_TYPE_PREFIX.STUDENT + mobile
                if old_guy:
                    if old_guy.id == seller_id:
                        return {'retcode': 1, 'msg': '无法给自己开户!'}
                    if (old_guy.level in [USER_LEVEL.LEVEL_SHENG, USER_LEVEL.LEVEL_SHI, USER_LEVEL.LEVEL_QU,
                                          USER_LEVEL.LEVEL_ENDORSEMENT, USER_LEVEL.LEVEL_OPERATOR,
                                          USER_LEVEL.LEVEL_PLATINUM]):
                        return {'retcode': 1, 'msg': '该用户已经是代理!'}
                    contract_res = mm_UserAuthentication.filter(student_id=old_guy.id, status=0, parent_id=seller_id).values("url").first()
                    if contract_res:
                        return {'retcode': 0, "msg": "ok", "data": contract_res['url']}
                    # parent, status = self.getDirectProxy(old_guy.id, needStatus=True)
                    # if parent and parent.id != seller_id and status == 1:
                    #     return {'retcode': 1, 'msg': '该用户已被其他代理商锁定!'}
                    mm_StudentTeam.update_or_create(student_id=old_guy.id, parent_id=seller_id,
                                                    defaults={"level": 1, "status": 2})
                    student_id = old_guy.id
                    # old_guy.level = level
                    old_guy.save()
                    # todo: 清理各种层级关系(done)
                    mm_StudentTeam.relieve_team(old_guy.id, seller_id)
                else:
                    adds_ret = self.addStudent(username, nick_name, phonenumber=mobile, level=0,
                                               status=2, share_code=share_code)

                    if adds_ret['retcode'] != 0:
                        transaction.savepoint_rollback(save_id)
                        return adds_ret
                    student_id = adds_ret['id']
                r = mm_StudentAgent.filter(student_id=student_id).values().first()
                if r:
                    mm_StudentAgent.filter(student_id=student_id).update(collection_name=nick_name, alipay_name=alipay_name,
                                           alipay_number=alipay_number, username=nick_name)
                else:
                    mm_StudentAgent.create(collection_name=nick_name, student_id=student_id, alipay_name=alipay_name,
                                           alipay_number=alipay_number, username=nick_name)
                # 将套餐放入缓存中
                datas = {"set_meal": set_meal, "seller_id": seller_id, "seller_level": seller_level, "level": level}
                cache.set("user_meal_"+str(student_id), json.dumps(datas), None)
                res = mm_UserAuthentication.get_execute_url(parent_id=seller_id, thirdPartyUserId=student_id,
                                                      name=alipay_name, type=2, company=alipay_name, mobile=mobile,
                                                      platform=platform)
                # from services.asyntask.paika_task import send_cards
                # for v in set_meal_data[set_meal]:
                #     send_ret = send_cards(student_id, v['nums'], None, seller_id, 2, level=level, role_id=v['goods_id'],
                #                           seller_level=seller_level)
                #     if send_ret['retcode'] != 0:
                #         transaction.savepoint_rollback(save_id)
                #         return send_ret
                return res
        except:
            transaction.savepoint_rollback(save_id)
            err = traceback.format_exc()
            model_logger.error(err)
            cache.delete("user_meal_"+str(old_guy.id))
            return {'retcode': 1, 'msg': "未知错误"}

    def getMyCardStats(self, studentid, role_id):
        total_key = CacheKeys.USER_TOTAL_CARD % (str(studentid), str(role_id))
        from services.datamodel.vip.models import UserCards, mm_UserCardLogs
        # 从数据库获取
        balance = UserCards.objects.filter(Q(expired_at__isnull=True) | Q(expired_at__gt=datetime.now()),
                                           status=0, student_id=studentid, role_id=role_id).count()
        total = cache.get(total_key)
        if not total:
            # total = mm_UserCardLogs.filter(buyer_student_id=studentid, channel__in=[2, 6], role_id=role_id).aggregate(
            #     sum=Coalesce(Sum("nums"), 0))
            # cache.set(total_key, total['sum'], 3600)
            # total = total['sum']
            total_data = mm_HistoryCache.get_cache_data_for_key(total_key)
            total = total_data['data']
        return {'balance': balance, 'total': total}

    def get_proxy_cert(self, studentObj):
        """
        获取代理商授权证书
        :param studentObj: student对象
        :return:
        """
        if studentObj.level == 0:
            return {'retcode': 1, 'msg': '非代理商无权限获取证书!'}
        proxy = mm_StudentAgent.get_or_default(student=studentObj)
        if proxy:
            cert = cache.get(CacheKeys.PROXY_CERT % studentObj.id)
            if cert:
                return {'retcode': 0, 'cert': cert}
            enterdate = proxy.created_at
            cert = genCert(studentObj.id, proxy.username, studentObj.mobile, enterdate.year, enterdate.month,
                           enterdate.day, enterdate.year + 1, enterdate.month, enterdate.day)
            cache.set(CacheKeys.PROXY_CERT % studentObj.id, cert, 3600 * 24 * 30)
            return {'retcode': 0, 'cert': cert}
        else:
            return {'retcode': 1, 'msg': '只有代理商才能获取证书!'}

    def modifyStudentByAdmin(self, studentid, request):
        student = self.get_or_default(id=studentid)
        if not student:
            return {
                'retcode': 404,
                'msg': 'id 为`{}`的学生不存在'.format(studentid)
            }

        for param in ['realname', 'schoolid', 'classid', 'gradeid',
                      'desc', 'phonenumber']:
            if param in request.param_dict:
                setattr(student, param, request.param_dict[param])

        try:
            with transaction.atomic():
                student.save()
                # 同时要更新user表里面的名字
                if 'realname' in request.param_dict:
                    student.user.last_name = request.param_dict['realname']
                    student.user.save()

        except IntegrityError as e:
            return {'retcode': 1, 'msg': "该手机号已被使用"}
        except:
            err = traceback.format_exc()
            model_logger.error(err)
            return {'retcode': 1, 'msg': "未知错误"}

        return {'retcode': 0}

    def modifyStudentByHimself(self, studentid, request):
        student = self.get_or_default(id=studentid)
        if not student:
            return {
                'retcode': 404,
                'msg': 'id 为`{}`的学生不存在'.format(studentid)
            }

        resp = {}
        params = [
            'realname',
            'age',
            'birth',
            'email',
            'address',
            'avatar_url',
            'desc',
            # 'phonenumber'
            'qq_number',
            'wechat_number',
            'student_phone',
            'home_phone',
            'parent_phone',
            'gender',
            'gradeid',
            'provinceid',
            'cityid',
            'districtid',
            'provincename',
            'cityname',
            'districtname'
        ]
        for param in params:
            if param in request.param_dict:
                setattr(student, param, request.param_dict[param])
                resp[param] = request.param_dict[param]
        if request.param_dict.get("avatar_url"):
            from services.datamodel.parent.models import (
                mm_ParentStudentRelation
            )
            pids = mm_ParentStudentRelation.filter(
                child_id=studentid,
            ).values_list("parent__id", flat=True)
            list(map(lambda pid: cache.delete(
                self.parentid2childreninfo.format(pid=pid),
            ), pids))

        # handle phone number change
        new_phonenumber = request.param_dict.get('phonenumber', '')
        # smsold = request.param_dict.get('smsold', '')
        smsnew = request.param_dict.get('smsnew', '')
        a_user = False
        if new_phonenumber and new_phonenumber != student.phonenumber:
            if smsnew:
                # check sms code
                smsRet = sender.checkcode(BusinessCode.WCHANGEPHONE,
                                          new_phonenumber, smsnew)
                if smsRet['retcode'] == 0:
                    student.phonenumber = new_phonenumber
                    a_user = True
                else:
                    return {'retcode': 2, 'msg': '手机验证码不正确'}
            else:
                return {'retcode': 3, 'msg': '需要手机验证码'}

        # 同时要更新user表里面的名字
        if 'realname' in request.param_dict:
            student.user.last_name = request.param_dict['realname']
            student.user.save()

        try:
            student.save()
            if a_user:
                student.user.username = USERNAME_TYPE_PREFIX.STUDENT + new_phonenumber
                student.user.save()
        except IntegrityError:
            return {'retcode': 1, 'msg': "手机号已经存在,请换一个手机号"}
        except:
            err = traceback.format_exc()
            model_logger.error(err)
            return {'retcode': 1, 'msg': "未知错误"}

        resp['retcode'] = 0

        return resp

    def changePasswordByAdmin(self, studentid):

        try:
            user = User.objects.get(student__id=studentid)
        except User.DoesNotExist:
            return {'retcode': 1, 'msg': '没有此用户'}

        user.password = make_password(self.DEFAULT_PASSWORD)

        try:
            user.save()
        except:
            return {'retcode': 1, 'msg': '修改用户密码失败'}

        return {'retcode': 0}

    def changePasswordByStudent(self, studentid, oldpassword, newpassword):

        try:
            user = User.objects.get(student__id=studentid)
        except User.DoesNotExist:
            return {'retcode': 1, 'msg': '没有此用户'}

        if not check_password(oldpassword, user.password):
            return {'retcode': 1, 'msg': '原密码错误'}

        user.password = make_password(newpassword)

        try:
            user.save()
        except IntegrityError:
            return {'retcode': 1, 'msg': '修改用户密码失败'}

        return {'retcode': 0}

    def getBasicInfo(self, studentid):
        student = self.get_or_default(id=studentid)
        if not student:
            return False, '无此学生信息'

        school = mm_School.get_or_default(id=student.schoolid)

        basicInfo = {
            'id': student.id,
            'ut': USER_TYPE.STDUENT,
            'realname': student.realname,
            'age': student.age,
            'gender': student.gender,
            'birth': student.birth.isoformat() if student.birth else None,
            'email': student.email,
            'address': student.address,
            'avatar_url': student.avatar_url,
            'desc': student.desc,
            'invitecode': student.invitecode,
            'phonenumber': student.phonenumber,
            'schoolname': getattr(school, "name", "未知学校"),
            # 'lastlogin': str(student.user.last_login)
            'qq_number': student.qq_number,
            'wechat_number': student.wechat_number,
            'student_phone': student.student_phone,
            'home_phone': student.home_phone,
            'parent_phone': student.parent_phone,
        }

        return True, basicInfo

    def getDetailInfo(self, id_or_instance, for_login=False, for_pad=False):
        if isinstance(id_or_instance, self.model):
            student = id_or_instance
        else:
            student = self.get_or_default(id=id_or_instance)
            if not student:
                return {"retcode": 1, "msg": "无此学生信息"}

        user = student.user
        resp = {
            "retcode": 0,
            "student_uid": user.id,
            "username": user.username
        }
        attr_list = [
            "id",
            "nick_name",
            "age",
            "gender",
            "avatar",
            "share_code",
            "mobile",
            "role_id",
            "role_expired_at",
            "provincename",
            "cityname",
            "districtname",
            "level",
            "birthday",
            "created_at",
            "mcc",
            "marriage",
            "education",
            "occupation",
            "openid",
            'is_live_permission'
        ]
        for attr in attr_list:
            resp[attr] = getattr(student, attr)

        # wni: 这段等后面加了推送再优化
        # if for_login:
        #     resp["notify_setting"] = {
        #         "receive_notify": student.receive_notify,
        #         "notify_with_sound": student.notify_with_sound,
        #         "notify_sound": student.notify_sound,
        #         "notify_shake": student.notify_shake,
        #     }
        #     if student.login_status is not True:
        #         student.login_status = True
        #         student.save()
        resp["level_name"] = USER_TYPE[student.level]
        couldview = 1 if student.role_expired_at and datetime.now() <= student.role_expired_at else 0
        resp['auth_view'] = couldview
        return resp

    def getUsrIdByStudentId(self, studentId):
        cachekey = self.student_sid2uid % studentId

        studentUid = cache.get(cachekey)
        if studentUid is None:
            student = self.get_or_default(id=studentId)
            if student:
                studentUid = student.user.id
                cache.set(cachekey, studentUid, 86400)
            else:
                studentUid = None

            return studentUid

    def searchStudentsWithPagenation(self, page, param_dict):
        result_values = [
            'id',
            'user_id',
            'realname',
            'age',
            'address',
            'classid',
            'invitecode',
            'phonenumber',
            'user__username',
            'parents',
            'schoolid',
            'user__date_joined',
            'gradeid',
            'balance'
        ]
        kwargs = {}

        username = param_dict.get('username')
        realname = param_dict.get('realname')
        phonenumber = param_dict.get('phonenumber')
        if username:
            kwargs['user__username__icontains'] = username
        if realname:
            kwargs['realname__icontains'] = realname
        if phonenumber:
            kwargs['phonenumber__icontains'] = phonenumber

        id_query_Q_list = []
        for key in ['schoolid', 'gradeid', 'classid']:
            if key not in param_dict:
                continue
            id_query_Q_list.append(Q(**{'%s__exact' % key: param_dict[key]}))
        result = self.filter(**kwargs)
        if id_query_Q_list:
            result = result.filter(reduce(and_, id_query_Q_list))
        result = result.prefetch_related(
            'parentstudentrelation_set',
        ).select_related('user').values(*result_values)

        school_ids = [one.get('schoolid') for one in result]
        class_ids = [one.get('classid') for one in result]
        schoolMap = mm_School.school_ids_to_school_names(school_ids)
        classMap = dict(mm_Class.filter(
            id__in=class_ids,
        ).values_list('id', 'name'))
        gradeMap = mm_Grade.list()['rettable']
        mm_School.all().aggregate()
        # TODO: optimization of performance
        result_filted = []
        ids = []
        for x in result:
            x['schoolname'] = schoolMap.get(x.get('schoolid'))
            x['classname'] = classMap.get(x.get('classid'))
            x['gradename'] = gradeMap.get(x.get('gradeid'))
            tf = "%Y-%m-%d %H:%M:%S"
            x['user__date_joined'] = x['user__date_joined'].strftime(tf)
            if x['user_id'] in ids:
                continue
            x['user__username'] = x['user__username'].replace('3*#*_', '')
            result_filted.append(x)
            ids.append(x['user_id'])

        # 没有指明页数，返回所有
        if not page:
            return {'retcode': 0, 'retlist': result_filted}

        paginator = Paginator(result_filted, 15)  # 15 per page

        try:
            result_page = paginator.page(page)
        except PageNotAnInteger:
            # If page is not an integer, deliver first page.
            result_page = paginator.page(1)
        except EmptyPage:
            # If page is out of range (e.g. 9999), deliver last page of results.
            result_page = paginator.page(paginator.num_pages)

        return {
            'retcode': 0,
            'retlist': list(result_page),
            'curpageidx': page,
            'hasnext': result_page.has_next(),
            'hasprevious': result_page.has_previous(),
        }

    def list_students_name_and_avatar(self, student_uids):
        infos = self.filter(user_id__in=student_uids).values(
            "realname",
            "user_id",
            "avatar_url",
            "id",
        )

        return {int(info["user_id"]): info for info in infos}

    def list_student_info(self, count=20, page=1,
                          root=None, valid=None, group=(), special_user=(),
                          name='', school_id=0, grade_id=0, class_id=0):
        """
        user_ids存放的是某个app的白名单的user id
        class id已经包含了学校年级信息，所以有class id就不需要school id和grade id
        如果没有class id就查看school id和grade id，只能指定某个学校的年级而不能指定所有年级
        """
        values = ['sid', 'user_id', 'realname', 'classid', 'phonenumber']
        q = Q()
        users = list(filter(bool, list(map(itemgetter('users'), group))))
        classes = list(filter(bool, list(map(itemgetter('classes'), group))))
        schools = list(filter(bool, list(map(itemgetter('schools'), group))))
        _special = defaultdict(list)
        for u in special_user:
            _special[u['could_install']].append(u['user_id'])

        group_q = Q(user__id__in=users) | Q(classid__in=classes) | Q(schoolid__in=schools) | Q(user__id__in=_special[1])
        special_q = ~Q(user__id__in=_special[0])
        level = special_user[0]['app__level'] if special_user else 0
        # 白名单用户
        if valid is 1:
            if level is not 1:
                q &= group_q
            q &= special_q
        # 非白名单用户
        elif valid is 0:
            if level is not 1:
                q &= ~group_q
            q |= ~special_q
        # pad的root或者非root学生列表
        elif root is not None:
            q &= Q(is_pad_root=root)
        # 没有初始化过滤条件
        else:
            values.append('is_pad_root')

        if name:
            q &= Q(realname__icontains=name)
        if class_id:
            q &= Q(classid=class_id)
        elif school_id:
            q &= Q(schoolid=school_id)
            if grade_id:
                q &= Q(gradeid=grade_id)

        user_list = self.filter(q).order_by('-id').annotate(sid=F('id')).values(*values)

        paginator = Paginator(user_list, count)
        try:
            users = paginator.page(page)
        except EmptyPage:
            users = []

        return {
            'retcode': 0,
            'user_list': list(users),
            'user_count': paginator.count,
            'user_page_count': paginator.num_pages,
        }

    def register_by_excel(self, school_id, grade_id, class_id, excel_file):
        """
        Excel 格式（带表头）：
        |姓名 | 手机号|
        |   |   |
        """
        user_info_list = []
        wrong_phone_list = []
        sheet = xlrd.open_workbook(file_contents=excel_file.read())
        table = sheet.sheet_by_name("Sheet1")
        for index, one_line in enumerate(table.get_rows()):
            if index == 0:
                continue
            realname = str(one_line[0].value).strip()
            phone = str(one_line[1].value).strip().split('.')[0]  # excel不带格式数值默认为float
            if self._is_valid_phone(phone):
                user_info_list.append((realname, phone))
            else:
                wrong_phone_list.append(phone)
        if wrong_phone_list:
            ret = {
                'retcode': 1,
                'msg': '表格中手机号格式错误：%s' % ' '.join(wrong_phone_list)
            }
            return ret
        if not user_info_list:
            ret = {
                'retcode': 1,
                'msg': '表格数据为空'
            }
            return ret
        existed_phone_list = self._check_account_exists([x[1] for x in user_info_list])
        if existed_phone_list:
            ret = {
                'retcode': 1,
                'msg': '表格中手机号已注册：%s' % ' '.join(existed_phone_list)
            }
            return ret
        with transaction.atomic():
            for name, phonenumber in user_info_list:
                username = USERNAME_TYPE_PREFIX.STUDENT + phonenumber
                self.addStudent(username=username, realname=name,
                                schoolid=school_id, gradeid=grade_id,
                                classid=class_id, phonenumber=phonenumber)
        ret = {
            'retcode': 0,
        }
        return ret

    @staticmethod
    def _check_account_exists(phone_list):
        """
        校验手机号是否已注册
        :param phone_list: 手机号列表
        :return:
        """
        existed_phone_list = []
        for phone in phone_list:
            username = USERNAME_TYPE_PREFIX.STUDENT + phone
            if User.objects.filter(username=username).exists():
                existed_phone_list.append(phone)
        return existed_phone_list

    @staticmethod
    def _is_valid_phone(phone):
        prog = re.compile('^1\d{10}$')
        if re.match(prog, phone):
            return True
        else:
            return False

    def get_student_list(self, page, num=15, phone=None, level=None, nickname=None, types=None, provinceid=None, uid=None,
                         super=None, channel=None, start_date=None, end_date=None, status=None, identity=None,
                         source=None, export=None, duration_sum_sort=None, last_day_sort=None):
        result = self.values("id", "mobile", "nick_name", "created_at", "level", "user_id", "updated_at", "unionid",
                             "share_code", "role_expired_at", "provinceid", "provincename", "headmaster_id",
                             "districtname", "districtid", "cityname", "cityid", "channel", "avatar", "identity",
                             "studentagent__status", "title").annotate(duration_sum=Sum('note_ranking__duration'), last_day=Max('note_ranking__day')).order_by("-created_at")
        if uid:
            result = result.filter(id=uid)
        if phone:
            result = result.filter(mobile=phone)
        if level:
            result = result.filter(level=level)
        if nickname:
            result = result.filter(nick_name__icontains=nickname)
        if types and int(types) == 1:
            result = result.filter(level__in=[0, 10])
        if types and int(types) == 2:
            result = result.filter(level__in=[1, 2, 3, 4, 5, 6])
        if provinceid:
            result = result.filter(provinceid=provinceid)
        if super:
            result = result.filter(level__in=[7, 9, 1, 2, 3])
        if channel:     # 不知为何原来筛选为parent_student__channel，报错改为channel。
            result = result.filter(channel=channel)
        if start_date and end_date:
            result = result.filter(created_at__range=[start_date, end_date])
        if status or status == 0:
            result = result.filter(studentagent__status=status)
        if identity and int(identity) in [0, 1]:
            result = result.filter(identity=identity)
        if source:
            result = result.filter(source=source)
        if duration_sum_sort and int(duration_sum_sort) in [0, 1]:
            if int(duration_sum_sort) == 0:
                result = result.order_by("duration_sum")
            if int(duration_sum_sort) == 1:
                result = result.order_by("-duration_sum")
        if last_day_sort and int(last_day_sort) in [0, 1]:
            if int(last_day_sort) == 0:
                result = result.order_by("last_day")
            if int(last_day_sort) == 1:
                result = result.order_by("-last_day")
        if export:
            from io import BytesIO
            from django.http import HttpResponse
            import xlwt
            ws = xlwt.Workbook(encoding="UTF-8")
            w = ws.add_sheet(u'数据')
            w.write(0, 0, u'用户编号')
            w.write(0, 1, u'用户昵称')
            w.write(0, 2, u'用户手机号')
            w.write(0, 3, u'用户授权时间')
            w.write(0, 4, u'用户身份')
            excel_row = 1
            for obj in result:
                w.write(excel_row, 0, obj['id'])
                w.write(excel_row, 1, obj['nick_name'])
                w.write(excel_row, 2, obj['mobile'])
                w.write(excel_row, 3, obj['created_at'].strftime('%Y-%m-%d %H:%M:%S'))
                w.write(excel_row, 4, obj['identity'])
                excel_row += 1
            sio = BytesIO()
            ws.save(sio)
            sio.seek(0)
            response = HttpResponse(sio.getvalue(), content_type='application/vnd.ms-excel')
            response['Content-Disposition'] = 'attachment; filename=stu_info.xls'
            response.write(sio.getvalue())
            return response
        if not page:
            return {'retcode': 0, 'retlist': list(result)}
        paginator = Paginator(result, num)
        count = result.aggregate(count=Count('id'))
        try:
            result_page = paginator.page(page)
        except PageNotAnInteger:
            result_page = paginator.page(1)
        except EmptyPage as e:
            return {'retlist': [], 'curpageidx': page,
                    'hasnext': False, 'hasprevious': False}
        return {'retlist': list(result_page), 'curpageidx': page,
                'hasnext': result_page.has_next(), 'hasprevious': result_page.has_previous(),
                'total_page': paginator.num_pages, 'count': count['count']}

    def get_evaluation_user(self, page, phone=None, level=None, nickname=None, uid=None, super=None, channel=None,
                            start_date=None, end_date=None, eva_channel=None, report_start_date=None, report_end_date=None):
        result = self.filter(channel=channel).select_related("cover_student", "answer_student"). \
            values("id", "nick_name", "mobile", "role_expired_at", "channel", "openid", "level", "created_at",
                   "updated_at", "avatar", "eva_channel_name", "answer_student__student_id__id",
                   "cover_student__parent_id__nick_name", 'answer_student__created_at', 'cover_student__level').order_by("-id")
        if uid:
            result = result.filter(id=uid)
        if phone:
            result = result.filter(mobile=phone)
        if level:
            result = result.filter(level=level)
        if nickname:
            result = result.filter(nick_name__icontains=nickname)
        if eva_channel:
            result = result.filter(eva_channel=eva_channel)
        if start_date:
            query = Q(created_at__range=[start_date, end_date])
            result = result.filter(query)
        if report_start_date:
            query = Q(answer_student__created_at__range=[report_start_date, report_end_date])
            result = result.filter(query)
        paginator = Paginator(result, 10)
        try:
            result_page = paginator.page(page)
        except PageNotAnInteger:
            result_page = paginator.page(1)
        except EmptyPage as e:
            return {'retlist': [], 'curpageidx': page,
                    'hasnext': False, 'hasprevious': False}
        return {'retlist': list(result_page), 'curpageidx': page,
                'hasnext': result_page.has_next(), 'hasprevious': result_page.has_previous(),
                'total_page': paginator.num_pages}

    def export_evaluation_users(self, start_date=None, end_date=None, channel=1, phone=None, evachid=None):
        try:
            from io import BytesIO
            from django.http import HttpResponse
            from services.datamodel.evaluation.models import mm_Answer
            from django.db.models import Max
            result = self.filter(channel=channel).select_related("cover_student", "answer_student").values(
                "id", "nick_name", "mobile", "role_expired_at", "channel", "openid", "level", "created_at",
                "updated_at", "eva_channel_name", "answer_student__student_id__id"
            ).annotate(c_stu_level=Max('cover_student__level'))
            if evachid:
                result = result.filter(eva_channel=evachid)
            if start_date:
                result = result.filter(Q(created_at__range=[start_date, end_date]))
            ws = xlwt.Workbook(encoding="UTF-8")
            w = ws.add_sheet(u'数据')
            w.write(0, 0, u'编号')
            w.write(0, 1, u'手机号')
            w.write(0, 2, u'用户昵称')
            w.write(0, 3, u'注册时间')
            w.write(0, 4, u'用户层级')
            w.write(0, 5, u'邀请人')
            w.write(0, 6, u'是否获取报告')
            w.write(0, 7, u'渠道')
            excel_row = 1
            for obj in result:
                m_st_obj = mm_StudentTeam.filter(student_id=obj['id'], level=1).values('parent__nick_name').first()
                p_nick_name = m_st_obj.get('parent__nick_name') if m_st_obj else None
                w.write(excel_row, 0, obj['id'])
                w.write(excel_row, 1, obj['mobile'])
                w.write(excel_row, 2, obj['nick_name'])
                w.write(excel_row, 3, obj['created_at'].strftime('%Y-%m-%d %H:%M:%S'))
                w.write(excel_row, 4, obj['c_stu_level'])
                w.write(excel_row, 5, p_nick_name)
                w.write(excel_row, 6, '是' if obj['answer_student__student_id__id'] else '否')
                w.write(excel_row, 7, obj['eva_channel_name'])
                excel_row += 1
            sio = BytesIO()
            ws.save(sio)
            sio.seek(0)
            response = HttpResponse(sio.getvalue(), content_type='application/vnd.ms-excel')
            response['Content-Disposition'] = 'attachment; filename=stu_info.xls'
            response.write(sio.getvalue())
            return response
        except Exception as e:
            model_logger.error(e)
            return {'retcode': 1, "msg": "失败"}

    def export_user(self, nrows, table):
        with transaction.atomic():
            # 创建事务保存点
            save_id = transaction.savepoint()
            ret = {'retcode': 0, "msg": "OK"}
            try:
                user_info_list = []
                for i in range(1, nrows):
                    row_value = table.row_values(i)  # 一行的数据
                    # 查询上级是否存在
                    agent = self.get_or_default(mobile=int(row_value[1]))
                    if not agent:
                        user_info_list.append(('上级代理', int(row_value[1])))
                        continue
                    # 查询下级代理
                    user = self.get_or_default(mobile=int(row_value[0]))
                    if not user:
                        nick_name = "优势" + str(random.randrange(1, 999999, 6))
                        res = self.addStudent(username=str(int(row_value[0]))+str(random.randrange(1, 999, 3)), realname=nick_name, phonenumber=int(row_value[0]), level=0,
                                              provinceid=0, provincename='', cityname='', cityid=0, districtid=0,
                                              districtname='', channel=6)
                        if res['retcode'] == 0:
                            user = mm_Student.get_or_default(id=res['id'])
                    if user:
                        if user.level == 0 and agent.level > user.level and agent.level in [1, 2, 3, 4, 5, 6]:
                            # 如果有上级先解绑
                            direct = mm_Student.getDirectProxy(user.id)
                            if direct:
                                if direct.id != agent.id:
                                    mm_StudentTeam.relieve_team(user.id, agent.id)
                                    self.binding_sup_team(user.id, agent.id)
                            # 与上级建立绑定（或者更改绑定关系）
                            else:
                                # 此情况
                                self.binding_sup_team(user.id, agent.id)
                        if user.level in [1, 2, 3, 4, 5, 6] and agent.level < user.level and agent.level in \
                                [1, 2, 3, 4, 5, 6]:
                            # 如果有上级先解绑
                            direct = mm_Student.getDirectProxy(user.id)
                            if direct:
                                if direct.id != agent.id:
                                    mm_StudentTeam.relieve_team(user.id, agent.id)
                                    self.binding_sup_team(user.id, agent.id)
                            # 与上级建立绑定（或者更改绑定关系）
                            else:
                                # 此情况
                                self.binding_sup_team(user.id, agent.id)
                        # else:
                        #     user_info_list.append(('该手机号不是代理或者等级不够', int(row_value[0])))
                    else:
                        user_info_list.append(('下级代理', int(row_value[0])))
                        # continue
                if user_info_list:
                    ret = {
                        'retcode': 1,
                        'msg': '表格中手机号不存在：%s' % ' '.join(str(i) for i in user_info_list)
                    }
            except Exception as e:
                err = traceback.format_exc()
                model_logger.error(err)
                transaction.savepoint_rollback(save_id)  # 回滚
                return {'retcode': 1, "msg": '出现错误...%s' % e}
            # 提交成功，显式的提交一次事务
            transaction.savepoint_commit(save_id)
        return ret

    def export_platform_user(self, nrows, table):
        with transaction.atomic():
            # 创建事务保存点
            save_id = transaction.savepoint()
            ret = {'retcode': 0, "msg": "OK"}
            try:
                for i in range(1, nrows):
                    row_value = table.row_values(i)  # 一行的数据
                    channel = int(row_value[2])
                    # 处理数据
                    mobile = int(row_value[0])
                    user = self.get_or_default(mobile=mobile)
                    if not user:
                        nick_name = "优势" + str(random.randrange(1, 999999, 6))
                        res = self.addStudent(username=mobile, realname=nick_name, phonenumber=mobile, level=0,
                                              provinceid=0, provincename='', cityname='', cityid=0, districtid=0,
                                              districtname='', channel=channel)
                        if res['retcode'] == 0:
                            user = mm_Student.get_or_default(id=res['id'])
                    if user.role_expired_at:
                        if user.role_expired_at < datetime.now():
                            origin_exp = datetime.now()
                        else:
                            origin_exp = user.role_expired_at
                    else:
                        origin_exp = datetime.now()
                    origin_exp += timedelta(days=int(row_value[1]))
                    user.role_expired_at = origin_exp
                    user.save()

            except Exception as e:
                model_logger.error(e)
                transaction.savepoint_rollback(save_id)  # 回滚
                return {'retcode': 1, "msg": '出现错误...%s' % e}
            # 提交成功，显式的提交一次事务
            transaction.savepoint_commit(save_id)
        return ret

    def binding_sup_team(self, user_id, sup_id):
        with transaction.atomic():
            save_id = transaction.savepoint()
            try:
                # 查询所有的上级
                mm_StudentTeam.update_or_create(student_id=user_id, parent_id=sup_id, defaults={"level": 1, "status": 2})
                sup_user = mm_StudentTeam.filter(student_id=sup_id).values("parent_id", "level")
                # user = mm_StudentTeam.filter(parent_id=user_id).values("student_id", "level")
                # for subordinate in user:
                #         mm_StudentTeam.update_or_create(student_id=subordinate['student_id'],
                #                                         parent_id=sup_id,
                #                                         defaults={"level": subordinate["level"] + 1, "status": 0})
                sup_list = [i['parent_id'] for i in sup_user]
                sups_user = mm_StudentTeam.filter(student_id__in=sup_list).values("parent_id", "level")
                for item in sups_user:
                    mm_StudentTeam.update_or_create(student_id=sup_id, parent_id=item["parent_id"],
                                                    defaults={"level": item["level"] + 1, "status": 0})
                level_two_list = [i['parent_id'] for i in sups_user]
                level_two_user = mm_StudentTeam.filter(student_id__in=level_two_list).values("parent_id", "level")
                for item in level_two_user:
                    mm_StudentTeam.update_or_create(student_id=sup_id, parent_id=item["parent_id"],
                                                    defaults={"level": item["level"] + 2, "status": 0})
                level_three_list = [i['parent_id'] for i in level_two_user]
                level_three_user = mm_StudentTeam.filter(student_id__in=level_three_list).values("parent_id", "level")
                for item in level_three_user:
                    mm_StudentTeam.update_or_create(student_id=sup_id, parent_id=item["parent_id"],
                                                    defaults={"level": item["level"] + 3, "status": 0})
                level_four_list = [i['parent_id'] for i in level_three_user]
                level_four_user = mm_StudentTeam.filter(student_id__in=level_four_list).values("parent_id", "level")
                for item in level_four_user:
                    mm_StudentTeam.update_or_create(student_id=sup_id, parent_id=item["parent_id"],
                                                    defaults={"level": item["level"] + 4, "status": 0})
                level_five_list = [i['parent_id'] for i in level_four_user]
                level_five_user = mm_StudentTeam.filter(student_id__in=level_five_list).values("parent_id", "level")
                for item in level_five_user:
                    mm_StudentTeam.update_or_create(student_id=sup_id, parent_id=item["parent_id"],
                                                    defaults={"level": item["level"] + 5, "status": 0})
            except Exception as e:
                model_logger.error(e)
                transaction.savepoint_rollback(save_id)  # 回滚
            transaction.savepoint_commit(save_id)
        return {'retcode': 0}

    def update_level(self, user_id):
        with transaction.atomic():
            # 创建事务保存点
            save_id = transaction.savepoint()
            ret = {'retcode': 0, "msg": "OK"}
            try:
                # 待定
                team_uid = mm_StudentTeam.filter(parent_id=user_id).values('student_id', 'level')
                direct = mm_Student.getDirectProxy(user_id)
                if direct:
                    for v in team_uid:
                        mm_StudentTeam.update_or_create(student_id=v['student_id'], parent_id=direct.id,
                                                        defaults={"level": v['level'] + 1, "status": 0})
                    mm_StudentTeam.filter(student_id=user_id, parent_id=direct.id).update(status=0, level=1)
                self.filter(id=user_id).update(level=0)
            except Exception as e:
                model_logger.error(e)
                transaction.savepoint_rollback(save_id)  # 回滚
                return {'retcode': 1, "msg": '出现错误...%s' % e}
            # 提交成功，显式的提交一次事务
            transaction.savepoint_commit(save_id)
        return ret

    def get_delivery_user(self, page, headmasterid=None, intention=None, mobile=None, source=None, is_pay=None,
                          export=None, launch_id=None, launch_dynamic_id=None, is_deal=None, is_wechat=None,
                          nick_name=None, is_report=None, start_date=None, end_date=None, channel=None, eva_channel_name=None,
                          distribution_start_data=None, distribution_end_data=None, export_bills=None):
        result = self.filter(headmaster_id__gt=0).select_related("answer_student"). \
            values("headmaster__nick_name", "avatar", "nick_name", "mobile", "intention", "is_deal", "created_at",
                   "channel", "source", "id", "eva_channel_name", "eva_channel", "studentchannel__launch_id",
                   'studentchannel__launch_dynamic_id', "studentchannel__is_wechat", 'answer_student__student_id__id',
                   "studentchannel__distribution_time"). \
            order_by("-created_at")
        if headmasterid:
            result = result.filter(headmaster_id=headmasterid)
        if intention:
            result = result.filter(intention=intention)
        if mobile:
            result = result.filter(mobile=mobile)
        if source:
            result = result.filter(source=source)
        if launch_id:
            idObj = mm_Delivery.filter(pid=launch_id).values('id')
            ids = [i['id'] for i in idObj]
            ids.append(launch_id)
            result = result.filter(studentchannel__launch_id__in=ids)
        if launch_dynamic_id:
            result = result.filter(studentchannel__launch_dynamic_id=launch_dynamic_id)
        if is_deal:
            result = result.filter(is_deal=is_deal)
        if is_wechat:
            result = result.filter(studentchannel__is_wechat=is_wechat)
        if nick_name:
            result = result.filter(nick_name=nick_name)
        if start_date and end_date:
            result = result.filter(created_at__range=[start_date, end_date])
        if distribution_start_data and distribution_end_data:
            result = result.filter(studentchannel__distribution_time__range=[distribution_start_data, distribution_end_data])
        if is_report:
            if int(is_report) == 0:
                result = result.filter(answer_student__isnull=True)
            else:
                result = result.filter(answer_student__isnull=False)
        if channel:
            result = result.filter(channel=channel)
        if eva_channel_name:
            result = result.filter(eva_channel_name__contains=eva_channel_name)
        if export:
            from io import BytesIO
            from django.http import HttpResponse
            import xlwt
            ws = xlwt.Workbook(encoding="UTF-8")
            w = ws.add_sheet(u'数据')
            w.write(0, 0, u'班主任')
            w.write(0, 1, u'用户头像')
            w.write(0, 2, u'用户手机号')
            w.write(0, 3, u'用户昵称')
            w.write(0, 4, u'用户意向')
            w.write(0, 5, u'用户注册时间')
            w.write(0, 6, u'是否成单')
            w.write(0, 7, u'来源')
            w.write(0, 8, u'测评渠道')
            w.write(0, 9, u'是否获取测试报告')
            w.write(0, 10, u'投放id')
            w.write(0, 11, u'活码id')
            w.write(0, 12, u'是否加好友')
            w.write(0, 13, u'公海标识别')
            w.write(0, 14, u'分配班主任时间')
            # w.write(0, 10, u'链接金额')
            # w.write(0, 11, u'支付方式')
            excel_row = 1
            for obj in result:
                w.write(excel_row, 0, obj['headmaster__nick_name'])
                w.write(excel_row, 1, obj['avatar'])
                w.write(excel_row, 2, obj['mobile'])
                w.write(excel_row, 3, obj['nick_name'])
                w.write(excel_row, 4, obj['intention'])
                w.write(excel_row, 5, obj['created_at'].strftime('%Y-%m-%d %H:%M:%S'))
                w.write(excel_row, 6, obj['is_deal'])
                w.write(excel_row, 7, obj['source'])
                w.write(excel_row, 8, obj['eva_channel_name'])
                w.write(excel_row, 9, obj['answer_student__student_id__id'])
                w.write(excel_row, 10, obj['studentchannel__launch_id'])
                w.write(excel_row, 11, obj['studentchannel__launch_dynamic_id'])
                w.write(excel_row, 12, obj['studentchannel__is_wechat'])
                w.write(excel_row, 13, UNCLAIMED.get(obj['channel']))
                w.write(excel_row, 14, obj['studentchannel__distribution_time'].strftime('%Y-%m-%d %H:%M:%S'))
                # w.write(excel_row, 10, charge / 100)
                # w.write(excel_row, 11, is_pay)
                excel_row += 1
            sio = BytesIO()
            ws.save(sio)
            sio.seek(0)
            response = HttpResponse(sio.getvalue(), content_type='application/vnd.ms-excel')
            response['Content-Disposition'] = 'attachment; filename=stu_info.xls'
            response.write(sio.getvalue())
            return response
        if export_bills:
            from io import BytesIO
            from django.http import HttpResponse
            from services.datamodel.order.models import mm_Bill
            import xlwt
            ws = xlwt.Workbook(encoding="UTF-8")
            w = ws.add_sheet(u'数据')
            w.write(0, 0, u'用户昵称')
            w.write(0, 1, u'手机号')
            w.write(0, 2, u'班主任名称')
            w.write(0, 3, u'是否成单')
            w.write(0, 4, u'购买课程名称')
            w.write(0, 5, u'金额')
            w.write(0, 6, u'支付方式')
            w.write(0, 7, u'支付时间')
            w.write(0, 8, u'支付平台')
            excel_row = 1
            for obj in result:
                bills = mm_Bill.filter(student_id=obj['id'], is_pay=1).values(
                    "is_pay", 'course', 'pay_time', 'payment_method', 'charge', 'platform')
                for bill in bills:
                    w.write(excel_row, 0, obj['nick_name'])
                    w.write(excel_row, 1, obj['mobile'])
                    w.write(excel_row, 2, obj['headmaster__nick_name'])
                    w.write(excel_row, 3, obj['is_deal'])
                    w.write(excel_row, 4, bill['course'])
                    w.write(excel_row, 5, bill['charge'])
                    w.write(excel_row, 6, bill['payment_method'])
                    w.write(excel_row, 7, bill['pay_time'].strftime('%Y-%m-%d %H:%M:%S'))
                    w.write(excel_row, 8, bill['platform'])
                    excel_row += 1
            sio = BytesIO()
            ws.save(sio)
            sio.seek(0)
            response = HttpResponse(sio.getvalue(), content_type='application/vnd.ms-excel')
            response['Content-Disposition'] = 'attachment; filename=stu_info.xls'
            response.write(sio.getvalue())
            return response
        paginator = Paginator(result, 10)
        try:
            result_page = paginator.page(page)
        except PageNotAnInteger:
            result_page = paginator.page(1)
        except EmptyPage as e:
            return {'retlist': [], 'curpageidx': page,
                    'hasnext': False, 'hasprevious': False}
        return {'retlist': list(result_page), 'curpageidx': page,
                'hasnext': result_page.has_next(), 'hasprevious': result_page.has_previous(),
                'total_page': paginator.num_pages}

    def get_delivery_user_detail(self, user_id):
        user = self.filter(id=user_id).values("headmaster__nick_name", "avatar", "nick_name", "mobile",
                                              "intention", "is_deal", "created_at", "channel", "source",
                                              "id", "gender", "level", "provinceid", "cityid", "districtid",
                                              "provincename", "cityname", "districtname", "created_at", "birthday",
                                              "education", "marriage", "occupation", "channel", "source", "eva_channel",
                                              "intention", "is_deal", "remarks", "age", "headmaster__mobile",
                                              "studentchannel__is_wechat")
        if len(user) <= 0:
            return None
        top_arr, dict_sorted_re = self.user_answer(user_id)
        return {'top_data': top_arr, 'recommend': dict_sorted_re, 'user': user[0]}

    def search_user_for_sandsms(self, page, mobile=None, nickname=None, start_date=None, end_date=None, headmaster_id=None):
        if headmaster_id:
            result = self.filter(headmaster_id=headmaster_id)
        else:
            result = self.filter(~Q(mobile=''))
        if mobile:
            result = result.filter(mobile=mobile)
        if nickname:
            result = result.filter(nick_name__contains=nickname)
        if start_date:
            query = Q(created_at__range=[start_date, end_date])
            result = result.filter(query)
        result = result.values("mobile", "id", "nick_name", "created_at").order_by("-created_at")
        paginator = Paginator(result, settings.DEFAULT_PAGESIZE)
        try:
            result_page = paginator.page(page)
        except PageNotAnInteger:
            result_page = paginator.page(1)
        except EmptyPage:
            result_page = paginator.page(paginator.num_pages)
        return {'data': list(result_page), 'curpageidx': page, 'hasnext': result_page.has_next(),
                'hasprevious': result_page.has_previous(), 'total_page': paginator.num_pages}

    def statistical_data(self, start_date=None, end_date=None, headmaster_id=None):
        from services.datamodel.order.models import mm_Bill
        if start_date and end_date:
            query = Q(created_at__range=[start_date, end_date])
            if headmaster_id:
                result = self.filter(headmaster_id=headmaster_id)
                query = query & Q(student__headmaster_id=headmaster_id)
            else:
                result = self.filter(headmaster_id__gt=0)
            counts = result.count()
            intention_a = result.filter(query).filter(intention=1).count()
            intention_b = result.filter(query).filter(intention=2).count()
            intention_c = result.filter(query).filter(intention=3).count()
            intention_d = result.filter(query).filter(intention=4).count()
            intention_e = result.filter(query).filter(intention=5).count()
            headmasters = self.filter(level=10).count()
            conversion_a = round(mm_Bill.filter(query).filter(is_pay=1, student__intention=1).count() / counts, 3) if counts else 0
            conversion_b = round(mm_Bill.filter(query).filter(is_pay=1, student__intention=2).count() / counts, 3) if counts else 0
            conversion_c = round(mm_Bill.filter(query).filter(is_pay=1, student__intention=3).count() / counts, 3) if counts else 0
            conversion_d = round(mm_Bill.filter(query).filter(is_pay=1, student__intention=4).count() / counts, 3) if counts else 0
            conversion_e = round(mm_Bill.filter(query).filter(is_pay=1, student__intention=5).count() / counts, 3) if counts else 0
            order_count = mm_Bill.filter(query).filter(is_pay=1).count()
            pay_conversion = round(order_count / counts, 3) if counts else 0
            pay_number_count = mm_Bill.filter(query).filter(is_pay=1).values('student_id').distinct().order_by("student_id").count()  # 成单人数
            # 成单金额 { 'charge__sum': 144 }
            charge_count = mm_Bill.filter(query).filter(is_pay=1).aggregate(Sum('charge'))
            yes_wechat_count = mm_StudentChannel.filter(query).filter(is_wechat=1).count()
            no_wechat_count = mm_StudentChannel.filter(query).filter(is_wechat=0).count()
            conversion_wechat = round(yes_wechat_count / counts, 3) if counts else 0
            course_a = round(mm_Bill.filter(query).filter(course=1).count() / counts, 3) if counts else 0
            course_b = round(mm_Bill.filter(query).filter(course=2).count() / counts, 3) if counts else 0
            course_c = round(mm_Bill.filter(query).filter(course=3).count() / counts, 3) if counts else 0
            course_d = round(mm_Bill.filter(query).filter(course=4).count() / counts, 3) if counts else 0
            course_e = round(mm_Bill.filter(query).filter(course=5).count() / counts, 3) if counts else 0
            return {'total': counts, 'intention_a': intention_a, 'intention_b': intention_b, 'intention_c': intention_c,
                    'intention_d': intention_d, 'intention_e': intention_e, 'headmasters': headmasters,
                    'conversion_a': conversion_a, 'conversion_b': conversion_b, 'conversion_c': conversion_c,
                    'conversion_d': conversion_d, 'conversion_e': conversion_e, 'order_total': order_count,
                    'pay_conversion': pay_conversion, 'pay_number_count': pay_number_count, 'charge_count': charge_count,
                    'yes_wechat_count': yes_wechat_count, 'no_wechat_count': no_wechat_count,
                    'conversion_wechat': conversion_wechat, 'course_a': course_a, 'course_b': course_b,
                    'course_c': course_c, 'course_d': course_d, 'course_e': course_e}

    def headmaster_statistical_data(self, page, headmaster_id=None, start_date=None, end_date=None, export=None):
        from services.datamodel.order.models import mm_Bill
        if headmaster_id:
            headmasters = self.filter(id=headmaster_id, level=10).values("id", "nick_name").order_by("-created_at")
        else:
            headmasters = self.filter(level=10).values("id", "nick_name")
        if export:
            result_page = headmasters
        else:
            paginator = Paginator(headmasters, 10)
            try:
                result_page = paginator.page(page)
            except PageNotAnInteger:
                result_page = paginator.page(1)
            except EmptyPage:
                result_page = paginator.page(paginator.num_pages)
        headmasters_data = []
        if start_date and end_date:
            query = Q(created_at__range=[start_date, end_date])
            for v in result_page:
                total_number = self.filter(query).filter(headmaster_id=v['id']).count()  # 服务用户数量
                intention_a = self.filter(query).filter(intention=1, headmaster_id=v['id']).count()
                intention_b = self.filter(query).filter(intention=2, headmaster_id=v['id']).count()
                intention_c = self.filter(query).filter(intention=3, headmaster_id=v['id']).count()
                intention_d = self.filter(query).filter(intention=4, headmaster_id=v['id']).count()
                intention_e = self.filter(query).filter(intention=5, headmaster_id=v['id']).count()
                conversion_a = round(mm_Bill.filter(query).filter(is_pay=1, student__intention=1, student__headmaster_id=v['id']).count() / total_number, 3) if total_number else 0
                conversion_b = round(mm_Bill.filter(query).filter(is_pay=1, student__intention=2, student__headmaster_id=v['id']).count() / total_number, 3) if total_number else 0
                conversion_c = round(mm_Bill.filter(query).filter(is_pay=1, student__intention=3, student__headmaster_id=v['id']).count() / total_number, 3) if total_number else 0
                conversion_d = round(mm_Bill.filter(query).filter(is_pay=1, student__intention=4, student__headmaster_id=v['id']).count() / total_number, 3) if total_number else 0
                conversion_e = round(mm_Bill.filter(query).filter(is_pay=1, student__intention=5, student__headmaster_id=v['id']).count() / total_number, 3) if total_number else 0
                # 成单总数
                order_count = mm_Bill.filter(query).filter(is_pay=1, student__headmaster_id=v['id']).count()
                pay_conversion = round(order_count / total_number, 3) if total_number else 0
                pay_number_count = mm_Bill.filter(query).filter(is_pay=1, student__headmaster_id=v['id']).values('student_id').distinct().order_by("student_id").count()  # 成单人数
                # 成单金额 { 'charge__sum': 144 }
                charge_count = mm_Bill.filter(query).filter(is_pay=1, student__headmaster_id=v['id']).aggregate(Sum('charge'))
                yes_wechat_count = mm_StudentChannel.filter(query).filter(is_wechat=1, student__headmaster_id=v['id']).count()
                no_wechat_count = mm_StudentChannel.filter(query).filter(is_wechat=0, student__headmaster_id=v['id']).count()
                conversion_wechat = round(yes_wechat_count / total_number, 3) if total_number else 0
                course_a = round(mm_Bill.filter(query).filter(course=1, student__headmaster_id=v['id']).count() / total_number, 3) if total_number else 0
                course_b = round(mm_Bill.filter(query).filter(course=2, student__headmaster_id=v['id']).count() / total_number, 3) if total_number else 0
                course_c = round(mm_Bill.filter(query).filter(course=3, student__headmaster_id=v['id']).count() / total_number, 3) if total_number else 0
                course_d = round(mm_Bill.filter(query).filter(course=4, student__headmaster_id=v['id']).count() / total_number, 3) if total_number else 0
                course_e = round(mm_Bill.filter(query).filter(course=5, student__headmaster_id=v['id']).count() / total_number, 3) if total_number else 0
                log_total = mm_StudentTrackRecord.filter(query).filter(student__headmaster_id=v['id']).count()
                headmaster_data = {'total_number': total_number, 'intention_a': intention_a, 'intention_b': intention_b, 'intention_c': intention_c,
                                   'intention_d': intention_d, 'intention_e': intention_e,
                                   'conversion_a': conversion_a, 'conversion_b': conversion_b, 'conversion_c': conversion_c,
                                   'conversion_d': conversion_d, 'conversion_e': conversion_e, 'order_total': order_count,
                                   'pay_conversion': pay_conversion, 'pay_number_count': pay_number_count, 'charge_count': charge_count,
                                   'yes_wechat_count': yes_wechat_count, 'no_wechat_count': no_wechat_count,
                                   'conversion_wechat': conversion_wechat, 'course_a': course_a, 'course_b': course_b,
                                   'course_c': course_c, 'course_d': course_d, 'course_e': course_e,
                                   'nick_name': v['nick_name'], 'id': v['id'], 'log_total': log_total}
                headmasters_data.append(headmaster_data)
        if export:
            from io import BytesIO
            from django.http import HttpResponse
            import xlwt
            ws = xlwt.Workbook(encoding="UTF-8")
            w = ws.add_sheet(u'数据')
            w.write(0, 0, u'班主任编码')
            w.write(0, 1, u'班主任名称')
            w.write(0, 2, u'服务用户数量（人）')
            w.write(0, 3, u'更单日志')
            w.write(0, 4, u'a类用户')
            w.write(0, 5, u'b类用户')
            w.write(0, 6, u'c类用户')
            w.write(0, 7, u'd类用户')
            w.write(0, 8, u'e类用户')
            w.write(0, 9, u'a类用户转化率')
            w.write(0, 10, u'b类用户转化率')
            w.write(0, 11, u'c类用户转化率')
            w.write(0, 12, u'd类用户转化率')
            w.write(0, 13, u'e类用户转化率')
            w.write(0, 14, u'总成单人数')
            w.write(0, 15, u'总成单数')
            w.write(0, 16, u'成单转化率')
            w.write(0, 17, u'成单总金额')
            w.write(0, 18, u'课程id1转化率')
            w.write(0, 19, u'课程id2转化率')
            w.write(0, 20, u'课程id3转化率')
            w.write(0, 21, u'课程id4转化率')
            w.write(0, 22, u'课程id5转化率')
            w.write(0, 23, u'已添加微信')
            w.write(0, 24, u'未添加微信')
            w.write(0, 25, u'加微率')
            excel_row = 1
            for obj in headmasters_data:
                w.write(excel_row, 0, obj['id'])
                w.write(excel_row, 1, obj['nick_name'])
                w.write(excel_row, 2, obj['total_number'])
                w.write(excel_row, 3, obj['log_total'])
                w.write(excel_row, 4, obj['intention_a'])
                w.write(excel_row, 5, obj['intention_b'])
                w.write(excel_row, 6, obj['intention_c'])
                w.write(excel_row, 7, obj['intention_d'])
                w.write(excel_row, 8, obj['intention_e'])
                w.write(excel_row, 9, obj['conversion_a'])
                w.write(excel_row, 10, obj['conversion_b'])
                w.write(excel_row, 11, obj['conversion_c'])
                w.write(excel_row, 12, obj['conversion_d'])
                w.write(excel_row, 13, obj['conversion_e'])
                w.write(excel_row, 14, obj['pay_number_count'])
                w.write(excel_row, 15, obj['order_total'])
                w.write(excel_row, 16, obj['pay_conversion'])
                w.write(excel_row, 17, obj['charge_count']['charge__sum'] / 100)
                w.write(excel_row, 18, obj['course_a'])
                w.write(excel_row, 19, obj['course_b'])
                w.write(excel_row, 20, obj['course_c'])
                w.write(excel_row, 21, obj['course_d'])
                w.write(excel_row, 22, obj['course_e'])
                w.write(excel_row, 23, obj['yes_wechat_count'])
                w.write(excel_row, 24, obj['no_wechat_count'])
                w.write(excel_row, 25, obj['conversion_wechat'])
                excel_row += 1
            sio = BytesIO()
            ws.save(sio)
            sio.seek(0)
            response = HttpResponse(sio.getvalue(), content_type='application/vnd.ms-excel')
            response['Content-Disposition'] = 'attachment; filename=stu_info.xls'
            response.write(sio.getvalue())
            return response
        else:
            return {'data': headmasters_data, 'curpageidx': page, 'hasnext': result_page.has_next(),
                    'hasprevious': result_page.has_previous(), 'total_page': paginator.num_pages}

    def export_statistical_data(self, headmaster_id=None, start_date=None, end_date=None):
        from services.datamodel.order.models import mm_Bill
        if headmaster_id:
            headmasters = mm_Bill.filter(student__headmaster_id=headmaster_id, is_pay=1)
        else:
            headmasters = mm_Bill.filter(is_pay=1)
        if start_date and end_date:
            headmasters = headmasters.filter(Q(created_at__range=[start_date, end_date]))
        headmasters = headmasters.values("id", "student__nick_name", "student__headmaster_id", "course",
                                         "payment_method", "charge", "student_id", "student__headmaster_id__nick_name",
                                         "updated_at").order_by("-created_at")
        from io import BytesIO
        from django.http import HttpResponse
        import xlwt
        ws = xlwt.Workbook(encoding="UTF-8")
        w = ws.add_sheet(u'数据')
        w.write(0, 0, u'班主任id')
        w.write(0, 1, u'班主任昵称')
        w.write(0, 2, u'成单课程')
        w.write(0, 3, u'成单金额')
        w.write(0, 4, u'成单时间')
        w.write(0, 5, u'支付方式')
        w.write(0, 6, u'购买用户')
        excel_row = 1
        for obj in headmasters:
            w.write(excel_row, 0, obj['student__headmaster_id'])
            w.write(excel_row, 1, obj['student__headmaster_id__nick_name'])
            w.write(excel_row, 2, obj['course'])
            w.write(excel_row, 3, obj['charge'])
            w.write(excel_row, 4, obj['updated_at'].strftime('%Y-%m-%d %H:%M:%S'))
            w.write(excel_row, 5, obj['payment_method'])
            w.write(excel_row, 6, obj['student__nick_name'])
            excel_row += 1
        sio = BytesIO()
        ws.save(sio)
        sio.seek(0)
        response = HttpResponse(sio.getvalue(), content_type='application/vnd.ms-excel')
        response['Content-Disposition'] = 'attachment; filename=stu_info.xls'
        response.write(sio.getvalue())
        return response

    def user_answer(self, user_id):
        from services.datamodel.evaluation.models import mm_Answer
        user_answer = mm_Answer.filter(student_id=user_id).first()
        # top_data = {}
        top_arr = []
        dict_sorted_re = []
        if not user_answer:
            return top_arr, dict_sorted_re
        if len(user_answer.top_four) == 1:
            return top_arr, dict_sorted_re
        # name = {1: '分析力', 2: '创新力', 3: '学习力', 4: '共情力', 5: '引领力', 6: '交往力', 7: '行动力', 8: '目标力', 9: '驱动力'}
        li_name = {'思维力': ['分析力', '创新力', '学习力'], '关系力': ['共情力', '引领力', '交往力'], '执行力':
            ['行动力', '目标力', '驱动力']}
        final = json.loads(user_answer.final_score)
        recommend_arr = {}
        i = 1
        for k, v in final.items():
            top_arr.append({'score': v['score'], 'name': k})
        top_arr = sorted(top_arr, key=lambda item: item['score'], reverse=True)
        for v in top_arr:
            v['sort'] = i
            i += 1
        top_four = json.loads(user_answer.top_four)
        for k in li_name:
            sum = 0
            for ks in top_four:
                if ks in li_name[k]:
                    sum += top_four[ks]
                    recommend_arr[k] = sum
        dict_sorted_re = sorted(recommend_arr.items(), key=lambda item: item[1], reverse=True)[0][0]
        return top_arr, dict_sorted_re

    def import_user_to_sale(self, nrows, table):
        with transaction.atomic():
            from services.datamodel.order.models import mm_Order
            # 创建事务保存点
            save_id = transaction.savepoint()
            ret = {'retcode': 0, "msg": "OK"}
            try:
                for i in range(1, nrows):
                    row_value = table.row_values(i)  # 一行的数据
                    user = self.get_or_default(mobile=int(row_value[1]))
                    if user and not user.headmaster_id:
                        user.source = 99
                        user.save()
                    elif user and user.headmaster_id:
                        # 有班主任但是班主任分配时长大于30天并且从未产生订单的用户，source标记为98
                        studentchannel_obj = mm_StudentChannel.filter(
                            student=user,
                            distribution_time__lte=datetime.now() + relativedelta(days=-30)
                        )
                        orders = mm_Order.filter(user=user, status=2)
                        if studentchannel_obj and not orders:
                            user.source = 98
                            user.save()
                    else:
                        resObj = User.objects.filter(username=int(row_value[1])).first()
                        if resObj:
                            resObj.delete()
                        nick_name = "优势" + str(random.randrange(1, 999999, 6))
                        res = self.addStudent(username=int(row_value[1]), realname=nick_name, level=0,
                                              provinceid=0, provincename='', cityname='', cityid=0, districtid=0,
                                              phonenumber=int(row_value[1]), districtname='', channel=6)
                        new_user = self.get_or_default(id=res['id'])
                        new_user.source = 99
                        new_user.save()
            except Exception as e:
                model_logger.error(e)
                transaction.savepoint_rollback(save_id)  # 回滚
                return {'retcode': 1, "msg": '出现错误...%s' % e}
            # 提交成功，显式的提交一次事务
            transaction.savepoint_commit(save_id)
        return ret

    def get_import_sale_user(self, page, mobile=None):
        result = self.filter(
            Q(source=99, headmaster_id=None) |
            Q(source=98, studentchannel__distribution_time__lte=datetime.now() + relativedelta(days=-30), orders__isnull=True)
        ).values("id", "nick_name", "mobile", "created_at", "avatar", "source").order_by("-created_at")

        if mobile:
            result = result.filter(mobile=mobile)
        paginator = Paginator(result, 15)
        try:
            result_page = paginator.page(page)
        except PageNotAnInteger:
            result_page = paginator.page(1)
        except EmptyPage as e:
            return {'retlist': [], 'curpageidx': page,
                    'hasnext': False, 'hasprevious': False}
        return {'retlist': list(result_page), 'curpageidx': page,
                'hasnext': result_page.has_next(), 'hasprevious': result_page.has_previous(),
                'total_page': paginator.num_pages}

    def export_screening_data(self, data):
        """
        导出筛查数据，生成新用户
        @param data:
        @return:
        """
        try:
            from django.http import HttpResponse
            import xlwt
            from project.settings_common import BASE_DIR
            ws = xlwt.Workbook(encoding="UTF-8")
            w = ws.add_sheet(u'数据')
            w.write(0, 0, u'手机号')
            w.write(0, 1, u'是否代言人')
            w.write(0, 2, u'昵称')
            w.write(0, 3, u'用户上级手机号')
            w.write(0, 4, u'用户上级昵称')
            excel_row = 1
            for v in data:
                mobile = v[0]
                is_agent = '否'
                parent_mobile = ''
                parent_nickname = ''
                stu = self.filter(mobile=mobile).first()
                if stu:
                    nick_name = stu.nick_name
                    if int(stu.level) in [1, 2, 3, 4, 5, 6]:
                        is_agent = '是'
                    # else:
                    #     # 同getDirectProxy方法，getDirectProxy方法必须保证跳级粉丝关系记录也存在
                    #     parent_team = mm_StudentTeam.filter(student=stu, level=1, status__in=[0,1,2]).first()
                    #     while parent_team:
                    #         if parent_team.parent.level in [1, 2, 3, 4, 5, 6]:
                    #             parent_mobile = parent_team.parent.mobile
                    #             break
                    #         parent_team = mm_StudentTeam.filter(student=parent_team.parent, level=1, status__in=[0,1,2]).first()
                    parent_team = mm_StudentTeam.filter(student=stu, level=1, status__in=[0,1,2]).first()
                    while parent_team:
                        if parent_team.parent.level in [1, 2, 3, 4, 5, 6]:
                            parent_mobile = parent_team.parent.mobile
                            parent_nickname = parent_team.parent.nick_name
                            break
                        parent_team = mm_StudentTeam.filter(student=parent_team.parent, level=1, status__in=[0,1,2]).first()
                else:
                    nick_name = "优势" + str(random.randrange(1, 999999, 6))
                    self.addStudent(username=int(mobile), realname=nick_name, level=0,
                                          provinceid=0, provincename='', cityname='', cityid=0, districtid=0,
                                          phonenumber=int(mobile), districtname='', channel=6)
                w.write(excel_row, 0, str(v[0]))
                w.write(excel_row, 1, is_agent)
                w.write(excel_row, 2, nick_name)
                w.write(excel_row, 3, str(parent_mobile))
                w.write(excel_row, 4, str(parent_nickname))
                excel_row += 1
            # /home/yjyx/yijiao_src/yijiao_main/static/stu_info.xls
            ws.save(f'{BASE_DIR}/static/stu_info.xls')
            return JsonResponse({"retcode": 0, "message": "导出成功", 'data': f'stu_info.xls'})
        except:
            err = traceback.format_exc()
            return JsonResponse({"retcode": 1, "message": "导出失败", "error": err})

    def export_phone_attribution(self, data):
        """
        导出手机号归属文件
        @param data:
        @return:
        """
        try:
            from django.http import HttpResponse
            import xlwt
            from project.settings_common import BASE_DIR
            import phone
            p = phone.Phone()
            ws = xlwt.Workbook(encoding="UTF-8")
            w = ws.add_sheet(u'数据')
            w.write(0, 0, u'手机号')
            w.write(0, 1, u'省')
            w.write(0, 2, u'市')
            excel_row = 1
            for v in data:
                try:
                    info = p.find(v[0])
                    province = info.get('province')
                    city = info.get('city')
                except:
                    province = ''
                    city = ''
                w.write(excel_row, 0, str(v[0]))
                w.write(excel_row, 1, province)
                w.write(excel_row, 2, city)
                excel_row += 1
            # /home/yjyx/yijiao_src/yijiao_main/static/phone_info.xls
            ws.save(f'{BASE_DIR}/static/phone_info.xls')
            return JsonResponse({"retcode": 0, "message": "导出成功", 'data': f'phone_info.xls'})
        except:
            err = traceback.format_exc()
            return JsonResponse({"retcode": 1, "message": "导出失败", "error": err})

class ModelManagerStudentTeam(BasicManager):

    def get_invite_history(self, studentid, page, stype):
        """
        获取邀请记录，目前是C端用户用，b端是客户管理
        :param stype: 0 注册用户；1 付费用户
        :param studentid:
        :param page: 获取第几页数据
        :return:
        """
        results = self.filter(parent_id=studentid, level=1, status=stype).select_related("student").order_by(
            "-updated_at").values("created_at").annotate(avatar=F("student__avatar"),
                                                         nick_name=F("student__nick_name"))

        paginator = Paginator(results, settings.DEFAULT_PAGESIZE)
        try:
            result_page = paginator.page(page)
        except PageNotAnInteger:
            result_page = paginator.page(1)
        except EmptyPage:
            result_page = paginator.page(paginator.num_pages)

        return {'retcode': 0, 'data': list(result_page), 'curpageidx': page, 'hasnext': result_page.has_next(),
                'hasprevious': result_page.has_previous()}

    def get_user_team(self, page, parent=None, level=None, student=None, status=None, channel=None, for_app=False,
                      is_complete=False):
        """

        :param page:
        :param parent:
        :param level:
        :param student:
        :param status:  wni： 注意， status是一个list, e.g. [0,1,2]
        :param for_app:
        :return:
        """
        if channel:
            qa = self.filter(student__channel=channel, parent_id=parent, status__in=status, level=1)
        else:
            qa = self.filter(parent_id=parent, status__in=status)
        results = qa.values(
            "parent_id", "student_id", "level", "status",
            "created_at", "student__nick_name", "student__avatar", "student__level", "student__role_expired_at",
            "student__mobile").order_by("-created_at")
        if level:
            results.filter(level=level)
        if student:
            results.filter(student_id=student)
        paginator = Paginator(results, settings.DEFAULT_PAGESIZE)
        try:
            result_page = paginator.page(page)
        except PageNotAnInteger:
            result_page = paginator.page(1)
        except EmptyPage:
            result_page = paginator.page(paginator.num_pages)

        if for_app:
            # result_page = list(result_page)
            for item in result_page:
                # balance_key = CacheKeys.USER_BALANCE % (str(item["student_id"]), '1')  # wni: 目前只统计399卡
                # total_key = CacheKeys.USER_TOTAL_CARD % (str(item["student_id"]), '1')
                summary = mm_Student.getMyCardStats(item["student_id"], 1)
                item['balance_cards'] = summary['balance']
                item['total_cards'] = summary['total']
                order_money_total = CacheKeys.ORDER_MONEY_TOTAL % (str(item["student_id"]))  # 代理总收益
                sale_total = mm_HistoryCache.get_cache_data_for_key(order_money_total)['data']
                item['sale_total'] = int(sale_total) if sale_total else 0
        if is_complete:
            from services.datamodel.evaluation.models import mm_Answer
            for item in result_page:
                item['is_complete'] = mm_Answer.filter(student_id=item['student_id']).exists()

        return {'data': list(result_page), 'curpageidx': page, 'hasnext': result_page.has_next(),
                'hasprevious': result_page.has_previous(), 'total_page': paginator.num_pages}

    def get_agent_student_list(self, page, phone=None, level=None, nickname=None, status=None, provinceid=None,
                               p_id=None, sta=None):
        result = self.filter(parent_id=p_id, student__level__in=status).values('student__nick_name', 'student__id',
                                                                       'student__mobile', 'student__created_at',
                                                                       'student__level', 'student__provincename',
                                                                       'student__cityname', 'student__districtname',
                                                                       'parent_id','student__studentagent__status').\
            order_by("-created_at")
        if level:
            result = result.filter(student__level=level)
        if phone:
            result = result.filter(student__mobile=phone)
        if nickname:
            result = result.filter(student__nick_name=nickname)
        if provinceid:
            result = result.filter(student__provinceid=provinceid)
        if sta or sta==0:
            result = result.filter(student__studentagent__status=sta)
        if not page:
            return {'retcode': 0, 'retlist': list(result)}
        paginator = Paginator(result, 15)
        try:
            result_page = paginator.page(page)
        except PageNotAnInteger:
            result_page = paginator.page(1)
        except EmptyPage as e:
            return {'retlist': [], 'curpageidx': page,
                    'hasnext': False, 'hasprevious': False}
        return {'retlist': list(result_page), 'curpageidx': page,
                'hasnext': result_page.has_next(), 'hasprevious': result_page.has_previous(),
                'total_page': paginator.num_pages}

    def relieve_team(self, student=None, parent=None):
        with transaction.atomic():
            save_id = transaction.savepoint()
            try:
                # 查询所有的上级
                parents = self.filter(student_id=student).select_for_update().values('parent_id')
                parent_ids = [i['parent_id'] for i in parents]
                self.filter(student_id=student).filter(~Q(parent_id=parent)).select_for_update().update(status=10)
                res = self.filter(parent_id=student, status=0).select_for_update().order_by('id').all()
                user_list = [i.student_id for i in res]
                self.filter(student_id__in=user_list, status=0, parent_id__in=parent_ids). \
                    filter(~Q(parent_id=student)).update(status=10)
            except Exception as e:
                model_logger.error(e)
                transaction.savepoint_rollback(save_id)  # 回滚
            transaction.savepoint_commit(save_id)
        return {'retcode': 0}

    def my_team_potential_user(self, page, user_id=None):
        results = self.filter(parent_id=user_id).filter(~Q(level=1)).values("id", "student__nick_name",
                                                                            "student__avatar", "created_at",
                                                                            "student_id").order_by("-created_at")
        paginator = Paginator(results, settings.DEFAULT_PAGESIZE)
        try:
            result_page = paginator.page(page)
        except PageNotAnInteger:
            result_page = paginator.page(1)
        except EmptyPage:
            result_page = paginator.page(paginator.num_pages)
        user_list = [i['student_id'] for i in result_page]
        res = self.filter(student_id__in=user_list, level=1).select_related("parent") \
            .order_by('id').values("student_id", "parent_id",
                                   "parent__nick_name")
        for v in result_page:
            for u in res:
                if v['student_id'] == u['student_id']:
                    v['parent_name'] = u['parent__nick_name']
        return {'retlist': list(result_page), 'curpageidx': page, 'hasnext': result_page.has_next(),
                'hasprevious': result_page.has_previous(), 'total_page': paginator.num_pages}

    def my_team_exclusive_user(self, page, user_id=None):
        results = self.filter(parent_id=user_id, level=1, status=0).values("id", "student__nick_name",
                                                                           "student__avatar", "created_at",
                                                                           "student_id", "student__mobile"). \
            order_by("-created_at")
        paginator = Paginator(results, settings.DEFAULT_PAGESIZE)
        try:
            result_page = paginator.page(page)
        except PageNotAnInteger:
            result_page = paginator.page(1)
        except EmptyPage:
            result_page = paginator.page(paginator.num_pages)
        return {'retlist': list(result_page), 'curpageidx': page, 'hasnext': result_page.has_next(),
                'hasprevious': result_page.has_previous(), 'total_page': paginator.num_pages}

    def my_team_pay_user(self, page, user_id=None):
        from services.datamodel.order.models import mm_Order
        results = self.filter(parent_id=user_id, status=1).values("id", "student__nick_name", "student__avatar",
                                                                  "created_at", "student_id").order_by("-created_at")
        paginator = Paginator(results, 6)
        try:
            result_page = paginator.page(page)
        except PageNotAnInteger:
            result_page = paginator.page(1)
        except EmptyPage:
            result_page = paginator.page(paginator.num_pages)
        for v in result_page:
            total_charge = mm_Order.filter(user_id=v['student_id'], status=2, agent_id=user_id). \
                aggregate(sum=Coalesce(Sum('charge'), 0))
            first_pay = mm_Order.filter(user_id=v['student_id'], status=2, agent_id=user_id).order_by("created_at"). \
                first()
            v['first_pay'] = ''
            if first_pay:
                v['first_pay'] = first_pay.created_at
            v['consumption'] = total_charge['sum']

        return {'retlist': list(result_page), 'curpageidx': page, 'hasnext': result_page.has_next(),
                'hasprevious': result_page.has_previous(), 'total_page': paginator.num_pages}

    def total_customer(self, user_id=None):
        potential_user = self.filter(parent_id=user_id, status=0).filter(~Q(level=1)).count()
        exclusive_user = self.filter(parent_id=user_id, status=0, level=1).count()
        pay = self.filter(parent_id=user_id, status=1).count()
        total = potential_user + exclusive_user + pay
        return {'total': total}

    def whether_gratis(self, user_id):
        result = self.filter(student_id=user_id, student__channel=1, level=1).first()
        if result:
            # 需要修改
            res = self.filter(parent_id=result.parent_id, student__channel=1, level=1).order_by('created_at')[:12]
            ids = [i.student_id for i in res]
            if user_id in ids:
                return True
        return False

    def free_places(self, user_id):
        return self.filter(parent_id=user_id, student__channel=2, level=1).count()

    def get_user_level(self, user_id):
        res = self.filter(student_id=user_id).exists()
        if res:
            return 1
        return 0

    def count_complete_number(self, user_id):
        from services.datamodel.evaluation.models import mm_Answer
        users = self.filter(parent_id=user_id, student__channel=1, level=1).values("student_id")
        u_id = [i['student_id'] for i in users]
        total = mm_Answer.filter(student_id__in=u_id).count()
        return {'total': total}

    def update_team_to_c(self, sup_phone, phone):
        sup_res = mm_Student.get_or_default(mobile=sup_phone)
        res = mm_Student.get_or_default(mobile=phone)
        if sup_res and res:
            if sup_phone.level > res.level and res.level == 0:
                old_team = self.get_or_default(student_id=res.id)
                if old_team:
                    old_team.status = 10
                    old_team.save()
                self.create(student_id=res.id, parent_id=sup_res.id, level=1, status=0)
                return True
        return False

    def export_users(self, start_date, end_date, channel=None):
        """

        @param start_date:
        @param end_date:
        @return:
        """
        try:
            from django.http import HttpResponse
            import xlwt
            from io import BytesIO
            users = mm_Student.filter(created_at__range=[start_date, end_date], level=0).values(
                'created_at', 'avatar', 'nick_name', 'mobile', 'id')
            if channel:
                users = users.filter(channel=channel)
            ws = xlwt.Workbook(encoding="UTF-8")
            w = ws.add_sheet(u'数据')
            w.write(0, 0, u'注册时间')
            w.write(0, 1, u'用户昵称')
            w.write(0, 2, u'用户手机')
            w.write(0, 3, u'上级代言人昵称')
            w.write(0, 4, u'上级代言人手机号')
            w.write(0, 5, u'代言人上级区代')
            w.write(0, 6, u'区代手机号')
            w.write(0, 7, u'市代昵称')
            w.write(0, 8, u'市代手机号')
            w.write(0, 9, u'省代昵称')
            w.write(0, 10, u'省代手机号')
            excel_row = 1
            for v in users:
                sheng_name = ''
                sheng_mobile = ''
                shi_name = ''
                shi_mobile = ''
                qu_name = ''
                qu_mobile = ''
                dai_name = ''
                dai_mobile = ''
                parent = self.filter(student_id=v['id'], level=1, status__in=[0,1,2]).values(
                    'parent__nick_name', 'parent__mobile', 'parent__level', 'parent_id').first()
                while parent:
                    if parent['parent__level'] == 1:
                        sheng_name = parent['parent__nick_name']
                        sheng_mobile = parent['parent__mobile']
                    elif parent['parent__level'] == 2:
                        shi_name = parent['parent__nick_name']
                        shi_mobile = parent['parent__mobile']
                    elif parent['parent__level'] == 3:
                        qu_name = parent['parent__nick_name']
                        qu_mobile = parent['parent__mobile']
                    elif parent['parent__level'] in [4, 5, 6]:
                        dai_name = parent['parent__nick_name']
                        dai_mobile = parent['parent__mobile']
                    parent = self.filter(student_id=parent['parent_id'], level=1, status__in=[0,1,2]).values(
                        'parent__nick_name', 'parent__mobile', 'parent__level', 'parent_id').first()
                w.write(excel_row, 0, v['created_at'].strftime('%Y-%m-%d %H:%M:%S'))
                w.write(excel_row, 1, v['nick_name'])
                w.write(excel_row, 2, v['mobile'])
                w.write(excel_row, 3, dai_name)
                w.write(excel_row, 4, dai_mobile)
                w.write(excel_row, 5, qu_name)
                w.write(excel_row, 6, qu_mobile)
                w.write(excel_row, 7, shi_name)
                w.write(excel_row, 8, shi_mobile)
                w.write(excel_row, 9, sheng_name)
                w.write(excel_row, 10, sheng_mobile)
                excel_row += 1
            sio = BytesIO()
            ws.save(sio)
            sio.seek(0)
            response = HttpResponse(sio.getvalue(), content_type='application/vnd.ms-excel')
            response['Content-Disposition'] = 'attachment; filename=stu_info.xls'
            response.write(sio.getvalue())
            return response
        except:
            err = traceback.format_exc()
            return JsonResponse({"retcode": 1, "message": "导出失败", "error": err})

class ModelManagerStudentCapital(BasicManager):

    def earning_summary(self, studentObj, page, role_id=1, stype=1):
        """

        :param studentObj:
        :param page:
        :param role_id:
        :param stype: 1 查今日收益； 2查本月收益
        :return:
        """
        from services.datamodel.vip.models import UserCards, mm_UserCardLogs
        start, end = Utils.getTodayDatetime() if stype == 1 else Utils.getMonthDatetime()
        results = self.filter(seller_student=studentObj, created_at__range=[start, end]).exclude(momey=0).select_related(
            "buyer_student").values("id", "momey", "buyer_student__avatar", "buyer_student__nick_name",
                                    "status", "created_at").order_by("-id")
        sys_total = self.filter(seller_student=studentObj).aggregate(sum=Sum("momey"))['sum']
        query_total = 0
        for item in results:
            query_total += item['momey']
        paginator = Paginator(results, settings.DEFAULT_PAGESIZE)
        try:
            result_page = paginator.page(page)
        except PageNotAnInteger:
            result_page = paginator.page(1)
        except EmptyPage:
            result_page = paginator.page(paginator.num_pages)

        sold = mm_UserCardLogs.filter(~Q(buyer_student_id=studentObj.id), seller_student_id=studentObj.id,
                                      created_at__range=[start, end], channel=4).aggregate(sum=Sum("nums"))
        sold_cards = sold['sum']

        return {'data': list(result_page), 'curpageidx': page, 'hasnext': result_page.has_next(),
                'hasprevious': result_page.has_previous(), 'total_page': paginator.num_pages,
                'sold_cards': sold_cards, 'sys_total': sys_total, 'query_total': query_total
                }

    def earning_peroid(self, studentObj, year, role_id=1):
        """
        app查询累积收益
        :param year:
        :param studentObj:
        :param role_id:
        :return:
        """
        order_money_total = CacheKeys.ORDER_MONEY_TOTAL % (str(studentObj.id))  # 代理总收益
        order_year = CacheKeys.ORDER_YEAR_DETAIL % (str(studentObj.id), str(year))
        # order_year_detail_json = cache.get(order_year)  # 获取年度订单详情
        order_year_detail_json = mm_HistoryCache.get_cache_data_for_key(order_year)['data']
        if order_year_detail_json:
            dict_detail = json.loads(order_year_detail_json)
            # dict_detail[month] = {'total_num': order_total, 'total_money': month_data['total'], 'month': month}
            result = sorted(dict_detail.values(), key=lambda d: d['month'], reverse=True)
        else:
            result = []

        frozen_total_key = CacheKeys.FROZEN_TOTAL % (str(studentObj.id))

        return {'data': result, 'frozen': cache.get(frozen_total_key),
                'total': mm_HistoryCache.get_cache_data_for_key(order_money_total)['data']
                }

    def earning_bymonth(self, studentObj, page, year, month):
        """
        获取给定年月的收益详情列表
        :param month: 几月，阿拉伯数字， 1-12
        :param year: 年份， 四位数字, 2021 .etc
        :param studentObj:
        :param page:
        :return:
        """
        # start, end = '%s-%02d-01 00:00:00' % (year, month), '%s-%02d-29 23:59:59' % (year, month)
        start, end = Utils.get_month_start_and_end_date(year, month)
        results = self.filter(seller_student=studentObj, created_at__range=[start, end]).exclude(momey=0).select_related(
            "buyer_student").values("id", "momey", "buyer_student__avatar", "buyer_student__nick_name",
                                    "status", "created_at").order_by("-id")
        paginator = Paginator(results, settings.DEFAULT_PAGESIZE)
        try:
            result_page = paginator.page(page)
        except PageNotAnInteger:
            result_page = paginator.page(1)
        except EmptyPage:
            result_page = paginator.page(paginator.num_pages)

        return {'data': list(result_page), 'curpageidx': page, 'hasnext': result_page.has_next(),
                'hasprevious': result_page.has_previous(), 'total_page': paginator.num_pages,
                }

    def earning_short(self, studentid, role_id=1):
        """
        app个人中心展现的收益汇总
        :param studentid:
        :param role_id:
        :return:
        """
        from services.datamodel.vip.models import UserCards, mm_UserCardLogs
        today_start, today_end = Utils.getTodayDatetime()
        month_start, month_end = Utils.getMonthDatetime()
        results = self.filter(seller_student_id=studentid, created_at__range=[month_start, month_end]).values(
            "id", "momey", "created_at")
        total = self.filter(seller_student_id=studentid).aggregate(sum=Coalesce(Sum("momey"), 0))['sum']
        month, today = 0, 0
        for item in results:
            month += item['momey']
            if today_start <= item['created_at'] <= today_end:
                today += item['momey']

        # order_money_total = CacheKeys.ORDER_MONEY_TOTAL % (str(studentObj.id))  # 代理总收益
        return {'retcode': 0, 'today': today, 'month': month, 'total': total,
                'balance': mm_Student.getMyCardStats(studentid, role_id)['balance']}

    def earning_list(self, page, agent_id=None, agent_name=None, agent_phone=None, start_date=None, end_date=None,
                     is_export=None):
        from services.datamodel.order.models import mm_Order
        results = self.filter(~Q(momey=0)).order_by("-updated_at").values("momey", "created_at", "before_money",
                                                                          "after_money", "status", "types",
                                                                          "buyer_student__mobile",
                                                                          "seller_student__nick_name",
                                                                          "seller_student__mobile",
                                                                          "seller_student__id", "seller_student_id",
                                                                          "order_id")
        if agent_id:
            results = results.filter(seller_student_id=agent_id)
        if start_date:
            query = Q(created_at__range=[start_date, end_date])
            results = results.filter(query)
        # if agent_name:
        #     agent = mm_Student.get_or_default(nick_name=agent_name)
        #     results = results.filter(agent_id=agent.id)
        # if agent_phone:
        #     agent = mm_Student.get_or_default(mobile=agent_phone)
        #     results = results.filter(agent_id=agent.id)
        agent_ids = [i['seller_student__id'] for i in results]
        agent_id_list = set(agent_ids)
        order_id_list = set([i['order_id'] for i in results])
        users = mm_StudentAgent.filter(student_id__in=agent_id_list).select_related("student"). \
            values('student__mobile', 'student_id', 'alipay_number', 'alipay_name', 'collection_name', 'username',
                   'student__nick_name')
        orders = mm_Order.filter(pk__in=order_id_list).values('payment', 'id', 'charge')
        for v in results:
            for vs in users:
                if v['seller_student_id'] == vs['student_id']:
                    v['agent_detail'] = vs
            for o_v in orders:
                if v['order_id'] == o_v['id']:
                    v['payment'] = o_v['payment']
                    v['charge'] = o_v['charge']
        if is_export:
            from io import BytesIO
            from django.http import HttpResponse
            import xlwt
            ws = xlwt.Workbook(encoding="UTF-8")
            w = ws.add_sheet(u'数据')
            w.write(0, 0, u'代理编号')
            w.write(0, 1, u'代理手机号')
            w.write(0, 2, u'代理姓名')
            w.write(0, 3, u'订单金额')
            w.write(0, 4, u'付款方式')
            w.write(0, 5, u'售出时间')
            w.write(0, 6, u'代理支付宝号')
            w.write(0, 7, u'代理支付宝姓名')
            w.write(0, 8, u'代理昵称')
            excel_row = 1
            for obj in results:
                if obj.get('agent_detail'):
                    student__mobile = obj['agent_detail']['student__mobile']
                    username = obj['agent_detail']['username']
                    alipay_number = obj['agent_detail']['alipay_number']
                    alipay_name = obj['agent_detail']['alipay_name']
                    student__nick_name = obj['agent_detail']['student__nick_name']
                else:
                    student__mobile = ''
                    username = ''
                    alipay_number = ''
                    alipay_name = ''
                    student__nick_name = ''
                if obj.get('payment'):
                    payment = obj['payment']
                else:
                    payment = 0
                w.write(excel_row, 0, obj['seller_student_id'])
                w.write(excel_row, 1, student__mobile)
                w.write(excel_row, 2, username)
                w.write(excel_row, 3, obj['momey'] / 100)
                w.write(excel_row, 4, payment)
                w.write(excel_row, 5, obj['created_at'].strftime('%Y-%m-%d %H:%M:%S'))
                w.write(excel_row, 6, alipay_number)
                w.write(excel_row, 7, alipay_name)
                w.write(excel_row, 8, student__nick_name)
                excel_row += 1
            sio = BytesIO()
            ws.save(sio)
            sio.seek(0)
            response = HttpResponse(sio.getvalue(), content_type='application/vnd.ms-excel')
            response['Content-Disposition'] = 'attachment; filename=stu_info.xls'
            response.write(sio.getvalue())
            return response
        paginator = Paginator(results, 15)
        try:
            result_page = paginator.page(page)
        except PageNotAnInteger:
            result_page = paginator.page(1)
        except EmptyPage:
            return {'retlist': [], 'curpageidx': page,
                    'hasnext': False, 'hasprevious': False}

        return {'retlist': list(result_page), 'curpageidx': page,
                'hasnext': result_page.has_next(), 'hasprevious': result_page.has_previous(),
                'total_page': paginator.num_pages}


class ModelManagerStudentAgent(BasicManager):

    def get_cert(self, studentObj):
        """
        代理获取授权证书
        :param studentObj:
        :return:
        """
        if studentObj.level not in [USER_LEVEL.LEVEL_SHENG, USER_LEVEL.LEVEL_SHI, USER_LEVEL.LEVEL_QU,
                                    USER_LEVEL.LEVEL_ENDORSEMENT, USER_LEVEL.LEVEL_OPERATOR, USER_LEVEL.LEVEL_PLATINUM]:
            return {'retcode': 1, "msg": "非代言人无法获取授权证书！"}

    def get_is_authentication(self, user_id):
        """
        获取是否认证
        :param user_id:
        :return:
        """
        cishu = cache.get('zhanshi_authentication_'+str(user_id))
        userObj = self.filter(student_id=user_id, student__level=0).values("status").first()
        if not userObj:
            return {'retcode': 0, "msg": "OK", "data": 2, "status": 1}
        if cishu == 4:
            return {'retcode': 0, "msg": "OK", "data": userObj['status'], "status": 1}
        cache_data = cache.get("user_meal_"+str(user_id))
        if cache_data:
            user_agent_data = json.loads(cache_data)
            if user_agent_data['level'] in [4, 5, 6]:
                if cishu:
                    cache.incr('zhanshi_authentication_'+str(user_id), 1)
                else:
                    cache.set('zhanshi_authentication_'+str(user_id), 1, None)
                return {'retcode': 0, "msg": "OK", "data": userObj['status'], "status": 0}
            if user_agent_data['level'] in [3]:
                return {'retcode': 0, "msg": "OK", "data": 2, "status": 1}
        return {'retcode': 0, "msg": "OK", "data": userObj['status'], "status": 0}

    def get_old_authentication(self, user_id):
        """
        针对老的代理（省市区）的认证
        :param user_id:
        :return:
        """
        cache_data = cache.get("user_meal_"+str(user_id))
        if cache_data:
            user_agent_data = json.loads(cache_data)
            if user_agent_data['level'] in [3]:
                return {'retcode': 0, "msg": "OK", "data": 0}
        userObj = self.filter(student_id=user_id, created_at__lte="2022-03-31 00:00:00",
                              student__level__in=[1,2,3]).values("status").first()
        if not userObj:
            return {'retcode': 0, "msg": "OK", "data": 2}
        return {'retcode': 0, "msg": "OK", "data": userObj['status']}

    def get_nocard(self, user_id):
        """
        针对用户已经实名和签完了合同，但是上级没卡的情况
        :param user_id:
        :return:
        """
        cishu = cache.get('zhanshi_nocard_'+str(user_id))
        if cishu == 4:
            return {'retcode': 0, "msg": "OK", "data": 0, "status": 1}
        faka_status = cache.get("faka_status_"+str(user_id))
        if faka_status:
            if cishu:
                cache.incr('zhanshi_nocard_'+str(user_id), 1)
            else:
                cache.set('zhanshi_nocard_'+str(user_id), 1, None)
            return {'retcode': 0, "msg": "OK", "data": 1, 'status': 0}
        return {'retcode': 0, "msg": "OK", "data": 0, 'status': 1}

    def authentication(self, user_id, types, page):
        """
        开户成功和待成功
        :param user_id:
        :param types:
        :param page:
        :return:
        """
        from services.datamodel.vip.models import mm_UserCards, mm_UserCardLogs
        result = mm_StudentTeam.filter(parent_id=user_id).values("student__nick_name", "student__mobile",
                                                                 "student__studentagent__created_at",
                                                                 "student__studentagent__status",
                                                                 "student__avatar", "student_id", "parent_id").order_by("-created_at")
        if types == 1:
            result = result.filter(student__studentagent__status=2)
        if types == 2:
            result = result.filter(student__userauthentication__status=1)
        paginator = Paginator(result, 15)
        try:
            result_page = paginator.page(page)
        except PageNotAnInteger:
            result_page = paginator.page(1)
        except EmptyPage:
            return {'retlist': [], 'curpageidx': page,
                    'hasnext': False, 'hasprevious': False}
        if types == 1:
            for v in result_page:
                user_capital = mm_StudentCapital.filter(seller_student_id=v['student_id']).values("after_money").last()
                if user_capital:
                    v['money'] = user_capital['after_money']
                else:
                    v['money'] = 0
                v['card_count'] = mm_UserCardLogs.filter(channel__in=[2, 6], buyer_student_id=v['student_id']).aggregate(
                sum=Coalesce(Sum("nums"), 0))['sum']
                v['sold'] = mm_UserCards.filter(student_id=v['student_id'], status=0).count()
        return {'retlist': list(result_page), 'curpageidx': page, 'hasnext': result_page.has_next(),
                'hasprevious': result_page.has_previous(), 'total_page': paginator.num_pages}

class ModelManagerStudentChannel(BasicManager):

    def check_parm(self, launch_id=0, launch_dynamic_id=0):
        """
        用户渠道二者选一
        :param launch_id:
        :param launch_dynamic_id:
        :return:
        """
        launch = mm_Delivery.filter(id=launch_id).last()
        launch_dynamic = mm_DynamicDelivery.filter(id=launch_dynamic_id).last()
        if launch or launch_dynamic:
            return {'retcode': 0}
        return {'retcode': 1, 'msg': '渠道参数错误'}

    def create_channel(self, student_id, launch_id=0, launch_dynamic_id=0, is_wechat=0, pay_class=0, tag=None):
        """
        用户创建渠道
        :param student_obj:
        :param launch_id:
        :param launch_dynamic_id:
        :param is_wechat:
        :param pay_class:
        :param tag:
        :return:
        """
        student_channel, _ = self.update_or_create(
            student_id=student_id,
            defaults={
                'student_id': student_id,
                'launch_id': launch_id,
                'launch_dynamic_id': launch_dynamic_id,
                'is_wechat': is_wechat,
                'pay_class': pay_class,
                'tag': tag,
            }
        )
        if launch_dynamic_id:  # 通过活码授权注册需要统计人数
            launch_dynamic = mm_DynamicDelivery.get(id=launch_dynamic_id)
            launch_dynamic.headmaster_uv += 1
            launch_dynamic.save()

    def get_channel_user(self, page, intention=None, launch_id=None, launch_dynamic_id=None, is_wechat=None,
                         headmaster_id=None, start_data=None, end_data=None):
        result = self.values("student_id", "student__mobile", "student__nick_name", "student__created_at")
        if intention:
            result = result.filter(student__intention__in=json.loads(intention))
        if launch_id:
            result = result.filter(launch_id=launch_id)
        if launch_dynamic_id:
            result = result.filter(launch_dynamic_id=launch_dynamic_id)
        if is_wechat:
            result = result.filter(is_wechat=is_wechat)
        if headmaster_id:
            result = result.filter(student__headmaster_id=headmaster_id)
        if start_data:
            result = result.filter(student__created_at__range=[start_data, end_data])
        paginator = Paginator(result, 10)
        try:
            result_page = paginator.page(page)
        except PageNotAnInteger:
            result_page = paginator.page(1)
        except EmptyPage as e:
            return {'retlist': [], 'curpageidx': page,
                    'hasnext': False, 'hasprevious': False}
        return {'retlist': list(result_page), 'curpageidx': page, 'hasnext': result_page.has_next(),
                'hasprevious': result_page.has_previous(), 'total_page': paginator.num_pages}

    def update_launch(self, mob_obj, wx_obj):
        mob_stu_cha = self.filter(student=mob_obj).first()
        wx_stucha = self.filter(student=wx_obj).values('launch_id', 'launch_dynamic_id').first()
        if not mob_stu_cha:
            self.create(student=mob_obj, launch_id=wx_stucha.get('launch_id', 0) if wx_stucha else 0,
                        launch_dynamic_id=wx_stucha.get('launch_dynamic_id', 0) if wx_stucha else 0)
        else:
            if not mob_stu_cha.launch_id and not mob_stu_cha.launch_dynamic_id:
                mob_stu_cha.launch_id = wx_stucha.get('launch_id', 0) if wx_stucha else 0
                mob_stu_cha.launch_dynamic_id = wx_stucha.get('launch_dynamic_id', 0) if wx_stucha else 0
                mob_stu_cha.save()

    def get_Workplace_DNA(self, student_id):
        stu_channel = self.filter(student_id=student_id).values("Workplace_DNA").first()
        if stu_channel:
           return stu_channel['Workplace_DNA']

class ModelManagerStudentTrackRecord(BasicManager):

    def create_log(self, student_id, track_log=''):
        """添加跟踪日志"""
        s = mm_Student.filter(id=student_id).first()
        if not s:
            return {'retcode': 1, 'message': '未找到该用户'}
        self.create(student=s, track_log=track_log)
        return {'retcode': 0}

    def get_log_list(self, student_id, page):
        """获取用户跟踪日志"""
        result = self.filter(student_id=student_id).values(
            'id',
            'created_at',
            'student__headmaster__id',
            'student__headmaster__nick_name',
            'track_log',
        ).order_by('-created_at')
        paginator = Paginator(result, 5)
        try:
            result_page = paginator.page(page)
        except PageNotAnInteger:
            result_page = paginator.page(1)
        except EmptyPage as e:
            return {'retlist': [], 'curpageidx': page,
                    'hasnext': False, 'hasprevious': False}
        return {'retlist': list(result_page), 'curpageidx': page,
                'hasnext': result_page.has_next(), 'hasprevious': result_page.has_previous(),
                'total_page': paginator.num_pages}

class ModelManagerStudentFans(BasicManager):

    def set_record(self, user_id, business, channel):
        userObj = mm_Student.get_or_default(share_code=business)
        cache_data = cache.get('url_user_'+str(user_id)+str(business)+str(channel))
        res = self.create(
                student_id=user_id,
                business_id=userObj.id,
                channel=channel,
                pv=1
            )
        if not cache_data:
            cache.set('url_user_'+str(user_id)+str(business)+str(channel), 1, 3600*10)
            res.uv=1
            res.save()
        return True

    def get_fans_list(self, page, user_id, name=None):
        result = self.filter(business_id=user_id)
        if name:
            user = mm_Student.filter(nick_name__contains=name).values('id')
            if not user:
                return {'retlist': [], 'curpageidx': page, 'hasnext': False, 'hasprevious': False}
            result = result.filter(student_id__in=[v['id'] for v in user])
        pv = 0
        uv = 0
        today = datetime.now().date()
        today_d = result.filter(created_at__range=[str(today) + ' 00:00:00', str(today) + ' 23:59:59']).aggregate(Sum('uv'))
        yesterday = (datetime.now() + timedelta(days=-1)).date()
        yesterday_d = result.filter(created_at__range=[str(yesterday) + ' 00:00:00',str(yesterday) + ' 23:59:59']).aggregate(Sum('uv'))
        result = result.values(
            'id',
            'created_at',
            'student_id',
            'student__nick_name',
            'student__avatar',
            'student__mobile',
            'business_id',
            'channel',
            'pv',
            'uv'
        ).order_by('-created_at')
        for item in result:
            pv += item['pv']
            uv += item['uv']
        paginator = Paginator(result, 10)
        try:
            result_page = paginator.page(page)
        except PageNotAnInteger:
            result_page = paginator.page(1)
        except EmptyPage as e:
            return {'retlist': [], 'curpageidx': page,
                    'hasnext': False, 'hasprevious': False}
        for v in result_page:
            f = mm_StudentTeam.get_or_default(parent_id=v['business_id'], student_id=v['student_id'])
            if f:
                v['status'] = 0
                v['level'] = f.level
            else:
                v['status'] = 10
        return {'retlist': list(result_page), 'curpageidx': page,
                'hasnext': result_page.has_next(), 'hasprevious': result_page.has_previous(),
                'total_page': paginator.num_pages, 'pv': pv, 'uv': uv, 'today_d': today_d['uv__sum'] if today_d else 0,
                'yesterday_d': yesterday_d['uv__sum'] if yesterday_d else 0}

class Student(models.Model):
    user = models.OneToOneField(settings.AUTH_USER_MODEL, related_name='student')
    age = models.PositiveSmallIntegerField(null=True, blank=True, default=0, verbose_name="年龄")
    avatar = models.CharField(max_length=150, null=True, blank=True, verbose_name="头像")  # 头像
    nick_name = models.CharField(max_length=30, null=True, blank=True, verbose_name="昵称")  # 昵称
    # password = models.CharField(max_length=150, verbose_name="密码")  # 密码
    gender = models.CharField(max_length=7, choices=(('male', '男'), ('female', '女')), default='female',
                              verbose_name='性别')  # unknown 保密
    # 手机号
    mobile = models.CharField(max_length=30, db_index=True, null=True, blank=True, unique=True, verbose_name="手机号")
    # 手机号国家码
    mcc = models.CharField(max_length=5, default="86", verbose_name="手机号国家码")
    # VIP套餐主键ID  ForeignKey
    role_id = models.PositiveSmallIntegerField(null=True, blank=True, default=0, verbose_name="VIP套餐主键ID")
    # VIP会员过期时间
    role_expired_at = models.DateTimeField(null=True, verbose_name="VIP会员过期时间")
    # # 上级ID
    # invite_user_id = models.PositiveIntegerField(null=True, blank=True, verbose_name="上级ID")  # 直接邀请人
    # # 和上级绑定的有效时间
    # invite_user_expired_at = models.DateTimeField(null=True, verbose_name="和上级绑定的有效时间")

    # 是否修改过密码 0是未修改  1是已修改
    is_password_set = models.PositiveSmallIntegerField(default=0, verbose_name="是否修改过密码 0是未修改  1是已修改")
    openid = models.CharField(max_length=30, blank=True, null=True, verbose_name="微信openid")  # 微信openid
    # 是否改修过昵称 0是未修改  1是已修改
    is_set_nickname = models.PositiveSmallIntegerField(default=0, verbose_name="是否改修过昵称 0是未修改  1是已修改")
    register_ip = models.CharField(max_length=30, null=True, blank=True, verbose_name="注册IP")  # 注册IP
    register_area = models.CharField(max_length=30, verbose_name="注册地区")  # 注册地区
    # 用户等级  0:普通C端 1:省代 2:市代 3:区代 4:运营商 5:铂金 6:代言人 7:运维 9:超管 10:班主任
    # wni: 在settings_common里面的USER_TYPE
    level = models.PositiveSmallIntegerField(default=0)
    # 用户唯一分享码 UUId
    share_code = models.CharField(max_length=50, unique=True, verbose_name="用户唯一分享码")
    provinceid = models.PositiveIntegerField(null=True, blank=True, verbose_name="省id")  # 省id
    cityid = models.PositiveIntegerField(null=True, blank=True, verbose_name="市id")  # 市id
    districtid = models.PositiveIntegerField(null=True, blank=True, verbose_name="区id")  # 区id
    provincename = models.CharField(max_length=100, null=True, blank=True, verbose_name="省名字")  # 省名字
    cityname = models.CharField(max_length=100, null=True, blank=True, verbose_name="市名字")  # 市名字
    districtname = models.CharField(max_length=100, null=True, blank=True, verbose_name="地区名字")  # 地区名字
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)
    birthday = models.DateField("生日", null=True)
    marriage = models.PositiveSmallIntegerField("婚姻", default=0)  # 1:已婚未育 2:未婚 3:已婚已育
    education = models.PositiveSmallIntegerField("学历", default=0)  # 1:高中 2:大专 3:大学本科 4:研究生 5:硕士 6:博士 7:博士后
    # 1:文职/编辑 2:IT从业者 3:自由职业/自主创业 4:K12教育/在校教师 5:新媒体自媒体/传媒/设计师 6:财务人员 7:国企/公务员 8:销售 9:服务业
    # 10:房地产/开发 11:全职妈妈 12:餐饮/服务业13:法律 14:服装外贸 15:广告/营销 16:仓储物流 17:HR 18:自动化/技术 19:公关 20:其他
    occupation = models.PositiveSmallIntegerField("工作", default=0)
    # 用户来源渠道 0:默认 1:(h5测评分享链接)    2:(APP测评分享链接)   3:(APP课程海报分享)   4:(APP邀请有礼海报)   5:(学籍卡海报分享)
    # 6:后台导入  7.投放  8.活码   9.抖音公海    10:亲子代言人测评  11:跨年演讲小程序助力转发  12:抽奖券小程序码  13:小程序授权登录  14:测评小程序码  15:直播
    channel = models.PositiveSmallIntegerField(null=True, blank=True, default=0)
    headmaster = models.ForeignKey("self", verbose_name="关联班主任", null=True, blank=True, on_delete=models.SET_NULL)  # 班主任
    # 0: 默认 1:抖音 2:公众号 3:知乎 4:支付宝 5:快手 6:百度 7:小红书 8:微博 9:360 10:爱奇艺, 11:小程序 99:该标记是为班主任未分配用户
    # 98:用户班主任分配时长大于30天且未产生订单用户  97:再分配用户 13: 职场小程序
    source = models.PositiveSmallIntegerField(default=0, verbose_name="用户来源")
    # 1:高度意向  2:意向一般  3:感兴趣  4:无明确表露  5:明确拒绝
    intention = models.PositiveSmallIntegerField("用户意向", default=0)
    is_deal = models.PositiveSmallIntegerField("是否成单", default=0)  # 0: 未成单  1:已成单
    eva_channel = models.PositiveIntegerField("测评渠道id", default=0)
    eva_channel_name = models.CharField("渠道名称", max_length=50, null=True)
    remarks = models.CharField("备注", default='', max_length=100)
    entry_time = models.IntegerField("入职年限", default=0, null=True, blank=True)
    applet_openid = models.CharField(max_length=30, blank=True, null=True, verbose_name="小程序openid")
    unionid = models.CharField(max_length=30, blank=True, null=True, verbose_name="unionid")
    is_live_permission = models.PositiveSmallIntegerField(default=0, verbose_name="是否有直播权限")  # 0：无，1：有
    status = models.PositiveSmallIntegerField("状态", default=1)  # 1:正常  2:禁言3天  3:永久禁言
    identity = models.PositiveSmallIntegerField("身份", default=0)
    title = models.CharField("头衔", max_length=20, default='')
    zb_unionid = models.CharField(max_length=30, blank=True, null=True, verbose_name="zb_unionid")
    zb_openid = models.CharField(max_length=30, blank=True, null=True, verbose_name="zb_openid")  # 微信openid


    objects = ModelManagerStudent()

    class Meta:
        db_table = "momself_student"


class StudentTeam(models.Model):
    student = models.ForeignKey(Student, related_name='cover_student')  # 被邀请人
    parent = models.ForeignKey(Student, related_name='parent_student')  # 邀请人
    level = models.PositiveSmallIntegerField(null=True, blank=True, default=0)  # 层级
    # 状态 0:粉丝 1:客户 2:代理 10:关系解除，进行伪删除
    status = models.PositiveSmallIntegerField(null=True, blank=True, default=0)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

    objects = ModelManagerStudentTeam()

    class Meta:
        db_table = "momself_student_team"
        unique_together = [("student", "parent")]


class StudentCapital(models.Model):
    """
      wni: 必须有支付行为后才涉及修改这张表
    """
    seller_student = models.ForeignKey(Student)
    momey = models.IntegerField("当前售价金额", default=0)  # 单位分
    before_money = models.IntegerField("收入前金额", default=0)
    after_money = models.IntegerField("收入后金额", default=0)
    status = models.PositiveSmallIntegerField("状态", default=1)  # 0：冻结中； 1：已入账；2: 已失效
    buyer_student = models.ForeignKey(Student, related_name='buyer_user')
    types = models.PositiveSmallIntegerField("类型", default=1)  # 1: 全额 2: 销售分佣 3: 流量分佣
    goods_id = models.IntegerField("商品ID", default=0)
    goods_type = models.IntegerField("商品类型标识符", default=1)  # 1：role； 2：course
    order_id = models.IntegerField("订单id", null=True, blank=True)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

    objects = ModelManagerStudentCapital()

    class Meta:
        db_table = "momself_student_capital"


class StudentAgent(models.Model):
    student = models.ForeignKey(Student)  # 被邀请人
    company_address = models.CharField("公司地址", null=True, max_length=100)
    card_positive = models.CharField("身份证正面", null=True, max_length=150)
    card_back = models.CharField("身份证反面", null=True, max_length=150)
    business_license = models.CharField("营业执照", null=True, max_length=150)
    business_license_number = models.CharField("营业执照号码", null=True, max_length=50)
    bank_code = models.CharField("银行卡号", null=True, max_length=50)
    bank_of_deposit = models.CharField("开户行", null=True, max_length=100)
    collection_name = models.CharField("收款人姓名", null=True, max_length=30)
    alipay_number = models.CharField("支付宝账号", null=True, max_length=30)
    alipay_name = models.CharField("支付宝姓名", null=True, max_length=30)
    realname = models.CharField("联系人名称", null=True, max_length=30)
    username = models.CharField("用户名", max_length=30)
    status = models.PositiveSmallIntegerField("状态", default=0)  # 创建代理状态 0:已创建未签合同 1:已签订合同
    account_id = models.CharField("易签宝的标识id", max_length=100, null=True, blank=True)
    id_number = models.CharField("易签宝证件号", max_length=100, null=True, blank=True)
    cert_no = models.CharField("证件号", max_length=100, null=True, blank=True)
    cert_type = models.CharField("证件类型", max_length=30, null=True, blank=True)
    bank_cardno = models.CharField("人银行卡号", max_length=50, null=True, blank=True)
    name = models.CharField("机构名称", max_length=100, null=True, blank=True)
    org_id = models.CharField("易签宝机构的标识id", max_length=100, null=True, blank=True)
    official_seal_url = models.TextField("代理公司url", default='', blank=True)
    seal_id = models.CharField("第三方公章id", default='', blank=True, max_length=100)
    gongmall_status = models.PositiveSmallIntegerField("公猫电签状态", default=1) # 1是未电签 2是已经电签
    withdrawal_pass = models.CharField("提现密码", default='', max_length=20)
    mobile = models.CharField("公猫手机号", default='', max_length=30)
    id_cards = models.CharField("公猫身份证", default='', max_length=100)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

    objects = ModelManagerStudentAgent()

    class Meta:
        db_table = "momself_student_agent"


class StudentChannel(models.Model):
    # wni：launch_id，launch_dynamicn_id都为空的两种情况：1、用户使用app端注册，2、老用户更新了是否加微信is_wechat字段
    student = models.OneToOneField(Student)  # 关联用户
    launch_id = models.IntegerField('投放id', null=True, blank=True)  # 投放列表id(主键id)
    launch_dynamic_id = models.IntegerField('活码id', null=True, blank=True)  # 活码列表id(主键id)
    is_wechat = models.PositiveSmallIntegerField("是否加微信", default=0, null=True, blank=True)  # 0否 1是 2未知
    pay_class = models.PositiveSmallIntegerField("够买课程", default=0, null=True, blank=True)
    tag = models.CharField("标签", max_length=100, null=True)
    distribution_time = models.DateTimeField("分配班主任时间", null=True, blank=True)
    Workplace_DNA = models.CharField("职场DNA", max_length=30, null=True, blank=True)

    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

    objects = ModelManagerStudentChannel()

    class Meta:
        db_table = "momself_student_channel"


class StudentTrackRecord(models.Model):
    student = models.ForeignKey(Student)
    track_log = models.CharField('跟踪日志', max_length=200, null=True, blank=True)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

    objects = ModelManagerStudentTrackRecord()

    class Meta:
        verbose_name = ''
        verbose_name_plural = verbose_name
        db_table = 'momself_student_track_record'

class StudentFans(models.Model):
    student = models.ForeignKey(Student, related_name='fans')
    business = models.ForeignKey(Student, related_name='superiors')
    channel = models.PositiveSmallIntegerField("浏览来之哪个渠道", default=0)
    pv = models.PositiveSmallIntegerField("pv", default=0)
    uv = models.PositiveSmallIntegerField("uv", default=0)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

    objects = ModelManagerStudentFans()

    class Meta:
        verbose_name = ''
        verbose_name_plural = verbose_name
        db_table = 'momself_student_fans'


mm_Student = Student.objects
mm_StudentTeam = StudentTeam.objects
mm_StudentCapital = StudentCapital.objects
mm_StudentAgent = StudentAgent.objects
mm_StudentChannel = StudentChannel.objects
mm_StudentTrackRecord = StudentTrackRecord.objects
mm_StudentFans = StudentFans.objects
